SubTotoal of field per year

[majordog]

Hey All,
I have a question concerning a query I am trying to write; I have a table with years and types

SELECT Veh_Year, COUNT(NoPlateType)AS COUNT
FROM dbo.AUG13_PM_RSD
GROUP BY Veh_Year, NoPlateType
WITH CUBE


What i want to do is get how many "reds" subtotoal were for each individual year so if there were 5 reds scattered in the table for the year 2000, it would display:

Veh_Year Count
2000 5

 

[SQLBill]

You could try:

SELECT Veh_Year, COUNT(NoPlateType)
FROM dbo.AUG13_PM_RSD
WHERE NoPlateType = 'red'
GROUP BY Veh_Year

-SQLBill

 

[majordog]

That:
SELECT Veh_Year, COUNT(NoPlateType)
FROM dbo.AUG13_PM_RSD
WHERE NoPlateType = 'LDV' OR NoPlateType = 'LDT'
GROUP BY Veh_Year


Gives me this as a result:

Veh_Year (No column Name)
NULL 583

I want it to display:

Veh_Year LDV LDT
1975 2 3
1976 4 5

 

[majordog]

Perhaps I'm approaching this wrong.. any help would be appreciated ..

I've tried something like this:

SELECT Veh_Year, NoPlateType AS LDV, NoPlateType AS LDT, COUNT(NoPlateType) AS SubTotal
FROM dbo.AUG15_PM_RSD
WHERE (NoPlateType = 'LDV') OR
(NoPlateType = 'LDT')
GROUP BY Veh_Year, NoPlateType
WITH CUBE

Which gives me results liek this:

LDT LDV SubTotal Veh_Year

LDT LDT 623 NULL
LDV LDV 65 NULL
NULL NULL 688 NULL
NULL NULL 688 NULL
LDT LDT 623 NULL
LDV LDV 65

AND This:

SELECT Veh_Year, SUM(Veh_Year) AS Count, NoPlateType, COUNT(NoPlateType) AS SubTotal
FROM dbo.AUG15_PM_RSD
GROUP BY Veh_Year, NoPlateType
WITH CUBE

This gives results like this:
COUNT NoPlateType SubTotal Year
3930 NULL 0 1965
3930 NULL 0 1966
1966 NULL 0 1966
1966 NULL 0 1969
1969 NULL 0 1969
1969 NULL 0 1970
5910 NULL 0 1970
5910 NULL 0 1972
7888 NULL 0