Grouping Counted Queries

[bruin03]

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

SELECT `description` , count( `description` ) AS thecount
FROM `dynamationlive`
GROUP BY  `description`
ORDER BY `thecount` DESC

[Mal Curtis]

Group By should be the thing you're grouping by, i.e. the part type

[r937]

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?

[bruin03]

r937, you are THE man! Thanks!

update:

I get an error when using my own column names:

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 "

[r937]

GROUP BY parttype