SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping Counted Queries

    I have a database structure that has 2 columsn: part and parttype

    I want to group the group the query by part types such that i can produce something that looks like

    doors, total doors (50)
    door1
    door2
    ...
    door50

    windows, total doors (3)
    windows1
    windows2
    windows3

    How do I do this?

    I can do parttype, total parts using this query
    Code MySQL:
    SELECT `description` , count( `description` ) AS thecount
    FROM `dynamationlive`
    GROUP BY  `description`
    ORDER BY `thecount` DESC

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Group By should be the thing you're grouping by, i.e. the part type

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT parttype
         , 'detail'    AS rowtype
         , NULL        AS thecount
         , part
      FROM dynamationlive
    UNION ALL
    SELECT parttype
         , 'total'     AS rowtype
         , COUNT(*)    AS thecount
         , NULL
    GROUP 
        BY parttype
    ORDER 
        BY parttype
         , rowtype DESC
         , part
    this query retrieves all the detail parts, as well as a count of all the parts in each parttype

    the detail and total rows are interleaved, with the totals row for a parttype coming before the detail part rows for that parttype

    neat, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    231
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, you are THE man! Thanks!

    update:

    I get an error when using my own column names:

    Code MySQL:
    SELECT `description`
         , 'detail'    AS rowtype
         , NULL        AS thecount
         , partnum
      FROM dynamationlive
    UNION ALL
    SELECT parttype
         , 'total'     AS rowtype
         , COUNT(*)    AS thecount
         , NULL
    GROUP 
        BY `description`
    ORDER 
        BY `description` DESC
         , rowtype DESC
         , partnum

    The error message reads:"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY `description` ORDER BY `description` DESC , rowtype DESC' at line 11 "

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    GROUP BY parttype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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