query with sub query

[Brianfree]

Hello, i am trying to put a query together but not able to have a count of tba or dos for each itmsgrpcod. It is just showing the same value for each row

SELECT T0.[ItmsGrpCod],
(SELECT COUNT(T0.[U_Category]) AS TBA FROM OITM T0 WHERE T0.[U_Category] = 'TBA') AS 'TBA',
(SELECT COUNT(T0.[U_Category]) AS DOS FROM OITM T0 WHERE T0.[U_Category] = 'DOS') AS 'DOS'
FROM OITM T0
GROUP BY T0.[ItmsGrpCod]

please can anyone help?

many thanks

Brian

 

[Olaf Doschke]

Well, you take out the computation of the TBA and DOS count fields as subqueries. In this way they are not done per group but you get the overall count.

To only count certain rows per group you can SUM(CASE WHEN T0.[U_Category] = 'TBA' THEN 1 ELSE 0 END) as "TBA" and similarly for DOS.

Bye, Olaf.

 

[SkipVought]

Hi,

SELECT DISTINCT....

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]