Sum of the count

Question

Sum of the count

matt101

Default port

Points: 1456
More actions
November 24, 2003 at 2:22 pm

#101770

I am trying to compute statistical information and well I ran into a slight problem with my new method of computing the values. The original way I did it works except for there is a lot of duplicated code. All I am doing is finding how how many accidents per month per year. I was wondering if it was possible to do it with one select or the 'orginal way' is the best method of doing this.
New Way with slight problem:
SELECT datepart(mm,date_requested) as my_month, datepart(year,date_requested) as the_year,count(date_requested) as my_count from accident_reg group by date_requested order by my_month,the_year
Problem with my new way is I need to sum the count but I can't seem to do that. I did get it to work with compute by but it creates seperate select lists and is not easy to work with on th VB.NET side. Any suggestions to how to 'Sum' the count up if its even possible.(I already tried Sum(Count(date_requested)))
The original Way:
SELECT '01' as the_month, '2002' as the_year, count(date_requested) from accident_reg where date_requested >= '1/1/2002' and date_requested <= '1/31/2002'
UNION
SELECT '01' as the_month,'2003' as the_year,count(date_requested) from accident_reg where date_requested >='1/1/2003' and date_requested <= '1/31/2003'
UNION
SELECT '02' as the_month, '2002' as the_year, count(date_requested) from accident_reg where date_requested >= '2/1/2002' and date_requested <= '2/28/2002'
UNION
SELECT '02' as the_month,'2003' as the_year,count(date_requested) from accident_reg where date_requested >='2/1/2003' and date_requested <= '2/28/2003'
UNION
SELECT '03' as the_month, '2002' as the_year, count(date_requested) from accident_reg where date_requested >= '3/1/2002' and date_requested <= '3/31/2002'
UNION
SELECT '03' as the_month,'2003' as the_year,count(date_requested) from accident_reg where date_requested >='3/1/2003' and date_requested <= '3/31/2003'
etc etc until I get to 12 to signify the 12 months.
Matt

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

eamonroche SSCommitted Points: 1678 More actions · Answer 1

I had a similar problem before. I slved it by doing the following in a stored procedure:

SELECT datepart(mm,date_requested) as my_month, datepart(year,date_requested) as the_year,count(date_requested) as my_count

into #temp_table

from accident_reg group by date_requested order by my_month,the_year

'FIRST RESULT SET

select * from #temp_table

'SECOND RESULT SET (ie the total)

select sum(my_count) from #temp_table

drop table #temp_table

Jonathan SSC-Insane Points: 20427 More actions · Answer 2



SELECT CONVERT(char(7),Date_Requested,120), COUNT(*)

FROM Accident_Reg

GROUP BY CONVERT(char(7),Date_Requested,120)

UNION ALL

SELECT 'Total', COUNT(*)

FROM Accident_Reg

If your Date_Requested column contains nulls, then use COUNT(Date_Requested) instead of COUNT(*).

--Jonathan

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Another way to skin the same cat =;o) - ROLLUP instead of UNION

SELECT CONVERT(char(7),Date_Requested,120),

COUNT(*)

FROM Accident_Reg

GROUP BY CONVERT(char(7),Date_Requested,120)

WITH ROLLUP

=;o)

/Kenneth

matt101 Default port Points: 1456 More actions · Answer 4

I was able to take Jonathan's idea to what exactly I need it.

SELECT CONVERT(tinyint,datepart(month,Date_Requested),120) as the_month,CONVERT(smallint,datepart(year,Date_Requested),120) as the_year, COUNT(date_requested) as accidents FROM Accident_Reg

GROUP BY CONVERT(tinyint,datepart(month,Date_Requested),120),CONVERT(smallint,datepart(year,Date_Requested),120)

I needed the month and year seperate as this is being used for my graphing application. Though I may go back to his original idea and to that portion in the vb.net to split it out but all the info is being processed on the same server so I am not 100% which method is best. The data I am using now only has 16 records in the db so I don't really see any difference between the 2 methods.

Matt

gljjr SSChampion Points: 10080 More actions · Answer 5

I know I'm a bit late in this but you should have been able to simply do something like the following....

 

SELECT datepart(mm,date_requested) as my_month

    , datepart(year,date_requested) as the_year

    , count(datepart(mm,date_requested)) as my_count 

from accident_reg 

group by datepart(mm,date_requested) 

order by datepart(mm,date_requested)

    ,  datepart(year,date_requested)

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.