Post reply

Sum of the count

  • 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

  • November 24, 2003 at 2:38 pm

    #483453

    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

  • November 24, 2003 at 2:52 pm

    #483455 

    

    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



    --Jonathan

  • November 25, 2003 at 4:33 am

    #483510

    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

  • November 25, 2003 at 11:21 am

    #483597

    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

  • December 2, 2003 at 5:22 pm

    #484389

    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.

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

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