MySQL: Row counts from mulitple tables in one shot...

Banacek

Hello, I have a quick question. I'm looking to get the row counts from multiple tables, and I can't figure out the best way to do this. I thought of maybe storing it in a view. Here's an example of what I have:

DROP VIEW IF EXISTS status;

CREATE ALGORITHM = TEMPTABLE VIEW status (diecount) AS
(SELECT COUNT(d.name) FROM die d WHERE d.wafer_name = "02H1") UNION
(SELECT COUNT(r.idreadsummary) FROM readsummary r
INNER JOIN die d
ON d.iddie = r.iddie WHERE d.wafer_name = "02H1");

This doesn't seem to work if I try to create another column to put the second select statement result in. I want just one row with different columns for the results from each select statement. What would the best way to do this? Again, it doesn't have to be a view, but I'll use it. Thanks!

bogu

And isn`t this working?

SELECT 
    COUNT(d.name), COUNT(r.idreadsummary) 
FROM readsummary r
INNER JOIN die d ON d.iddie = r.iddie 
WHERE d.wafer_name = "02H1"

Why do u need UNION?

Banacek

bogu wrote:

And isn`t this working?

SELECT 
    COUNT(d.name), COUNT(r.idreadsummary) 
FROM readsummary r
INNER JOIN die d ON d.iddie = r.iddie 
WHERE d.wafer_name = "02H1"

Why do u need UNION?

The problem is that I just need the rows from the first table, then use those id's to find the rows from the second table. Using the code above COUNT will be the same for everything since there's going to be only on number of rows, if that makes any sense 🙂

bogu

Banacek wrote:
I want just one row with different columns for the results from each select statement.

What exactly do u need to find?
U need to find for each d.iddie how many r.idreadsummary are?

that should be something like this:

SELECT 
    d.iddie, COUNT(r.idreadsummary) as nr
FROM die d 
LEFT JOIN readsummary r USING(iddie)
GROUP BY d.iddie

Maybe u can give us an example with your two tables with a few rec., how the table are linked with each other and what results do u want ..

Banacek

bogu wrote:
What exactly do u need to find?
U need to find for each d.iddie how many r.idreadsummary are?

that should be something like this:
SELECT 
    d.iddie, COUNT(r.idreadsummary) as nr
FROM die d 
LEFT JOIN readsummary r USING(iddie)
GROUP BY d.iddie
Maybe u can give us an example with your two tables with a few rec., how the table are linked with each other and what results do u want ..

I know, I'm not doing the best job explaining it. Let me show the tables first:

table die
iddie name
1 02H1
2 02H1
3 03G0

...and so on. the readsummary table has the following:

table readsummary
idreadsummary iddie
1 1
2 1
3 1
4 1
5 2

...and so on. What I want to do is get the row count where name equals something, let's say 0H1. For the above example that would be 2. Then I want to get the row count for where those iddie's are found in the readsummary table, which would be 5. When I tried using joins I got 5 back for both row counts, which isn't correct. Normally I could do more than one SELECT command, but it tends to add up on our server, so I'mm looking for a way to consolidate the SQL code. Again, thanks for your help!

Banacek

I think this might be what I was looking for:

SELECT COUNT(d.iddie), SUM(rs.rscount)
FROM die d
JOIN (
  SELECT COUNT(r.idreadsummary)AS rscount, r.iddie FROM readsummary r
  GROUP BY r.iddie) AS rs
  ON d.iddie = rs.iddie
WHERE d.wafer_name = "02H1";

Let me know if that looks correct. Thanks.

bogu

For mysql 4.0:

(SELECT COUNT(iddie) as nr_rec, 'die' as `table` FROM die WHERE `name` LIKE '02H1')
UNION
(SELECT
	COUNT(idreadsummary), 'readsummary'
FROM readsummary t1
LEFT JOIN die t USING(iddie)
WHERE `name` LIKE '02H1')

For mysql 4.1 or bigger:

SELECT
	COUNT(iddie) as nr_die,
	(SELECT
		COUNT(idreadsummary)
	 FROM readsummary t1
	 LEFT JOIN die t USING(iddie)
	 WHERE t.`name` LIKE '02H1') as nr_reads
FROM die 
WHERE `name` LIKE '02H1'