Getting the total count

[janise]

This is supposed to be easy
I am trying to do a query to get a total monthly violatins by dept,
Why is this query not working?
select cast(datepart("m",accidentDate) as varchar)+'/'+ cast(datepart("yyyy",accidentDate) as varchar) as "Monthly violations", deptname,count(violationID) from violation;

the result we expect is:
01/2003 02/2003 03/2003 04/2003 etc
3 for dept1 3 for dept1 7 for dept1 1 fr dept1
1 for dept2 1 for dept2 1 for dept2 1 fr dept2
7 for dept3 7 for dept3 1 for dept3 0 fr dept3

Instead f getting this vaues abve, I am getting ike:
exampe 1 for dept1
1 for dept1
1 for dept1 etc
It is not getting me the summary total instead it is giving me the count one number at a time

 

[dombeneteau]

Try adding a "group by" clause (eg: group by deptname) at the end of your query.
Hope it helps

 

[pascalsql]

Group by is necessary but not sufficent. Your problem is a matrix transformation, which is a little more tricky because you can have many columns.

To begin with, try:

select
datepart('yyyy', accidentDate) AccYear,
datepart('m', accidentDate) AccMonth,
deptname,
count(violationID) AccNumber
into #tempo
from violation
group by datepart('yyyy', accidentDate), datepart('m', accidentDate), deptname
order by AccYear, AccMonth, deptname

You get a temporary table easier to query and transform in a matrix.


(note: I can't check the above syntax, no sqlserver at home)