|
Home > Archive > Microsoft SQL Server forum > September 2005 > COUNT() Idiot
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| gregfocker@earthlink.net 2005-09-01, 8:23 pm |
| I have three tables: table1 has a one-to-many relationship with both
table2 and table3. When I do a left join to get a count from table2,
it works:
SELECT table1.field1, COUNT(table2.field1) as MyCount
FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1
WHERE...
GROUP BY table1.field1
I want to also get a count from table3:
SELECT table1.field1, COUNT(table2.field1) as MyCount,
COUNT(table3.field1) as MyOtherCount
FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1
LEFT JOIN table3 ON table1.field1 = table3.field1
WHERE...
GROUP BY table1.field1
The Count totals from the above query end up being a multiple of the
totals that I actually want. (If there are 8 records each in table2 and
table3, the counts would be 64 for each table). Can someone help me
get the proper counts, in this case 8 from table2 and 8 from table3.
Thanks!
| |
|
| a guess:
could be COUNT(DISTINCT table2.field1) as MyCount,
COUNT(DISTINCT table3.field1) as MyOtherCount
| |
| --CELKO-- 2005-09-01, 8:23 pm |
| SELECT T1.field1,
(SELECT COUNT (field1) FROM Table2
WHERE T1.field1 = T2.field1) AS t2_total,
(SELECT COUNT (field1) FROM Table3
WHERE T1.field1 = T3.field1) AS t3_total,
FROM Table1 AS T1
WHERE...
GROUP BY T1.field1;
|
|
|
|
|