Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    3

    Unanswered: aggregate of an aggregate

    I have a result set that is a group by. I need to aggregate that result. The query is :
    select count(*) as cnt, date from reports group by date order by cnt desc

    and the result is :

    cnt date
    ---------- ----------------
    5 2008-12-04
    4 2008-06-18
    4 2008-08-05
    4 2008-08-25
    4 2008-09-12
    4 2008-09-17
    4 2008-09-19
    4 2008-10-18
    4 2008-11-20
    4 2008-11-21
    4 2008-12-29
    4 2009-04-23
    4 2009-07-24
    3 2008-06-11
    3 2008-07-11
    3 2008-07-16

    .
    .
    .
    .
    1 2005-04-01

    I now need to just see a group by of "cnt' which would look like :

    1 5 'There is one "record" with the value 5
    12 4 ' there are 12 "records" with the value 4
    3 3 ' there are 3 "records" with the value 3
    .
    .
    .
    .
    .
    1 1 'there are 1 "records" with the value 1

    The table is a database of inspections. This final result will say there where 12 days with 4 inspections, 3 days with 3 inspections, one day with 5, etc.....(depending on the actual data).

    Thanks mucho

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT cnt
         , COUNT(*) AS count_of_cnt
      FROM ( SELECT COUNT(*) AS cnt
                  , date 
               FROM reports 
             GROUP 
                 BY date ) AS d
    GROUP
        BY cnt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    3
    Thanks r937.

    To get results restricted by date, would I put "where date ='xx/xx/xx" right after "From reports " or after "By date ) " or at both locations?

    Thanks
    PS

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    put it in the subquery

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •