Re: ORDER BY question

[Guelphdad Lake]

If you don't need an ORDER BY clause then you can use WITH ROLLUP on your GROUP BY clause:

SELECT
  make,
  COUNT(*) as total
FROM
  yourtablename
GROUP BY
  make
WITH ROLLUP

there are two things wrong with that though
1) you wanted specific order for your results
2) you've got that ugly NULL referencing the total of all vehicles

You can solve this by getting the count of all cars separately from your query about the separate counts for the cars. Here it is first in two queries:

SELECT
  make,
  COUNT(*) as total
FROM
  yourtablename
GROUP BY
  make
ORDER BY FIELD(make, 'Dodge','Toyota','Honda',make)

that gives you your totals for each make of cars and orders them by those specific cars at the top, and then by the rest of the makes.

though that would not order the other makes of cars by total, which you may want, so add an order by total as well like this:

SELECT
  make,
  COUNT(*) as total
FROM
  yourtablename
GROUP BY
  make
ORDER BY FIELD(make, 'Dodge','Toyota','Honda',make),
  total DESC

which says return Dodge, Toyota, Honda in that specific order, regardless of number of cars, and then the rest of the makes below that and those ordered by total descending.

Got that part right?

Now a simple total for all makes:

SELECT
  'Total Cars',
  COUNT(*)
FROM
  yourtable

You might wonder why the 'Total Cars' is in quotes, that's because we need to use it in place of NULL you got out of the WITH ROLLUP clause up above.

So now you want a UNION ALL to bring both queries together. There's an error here I'll fix in a minute but wanted to show you the query without messing it up too much:

SELECT
  make,
  COUNT(*) as total
FROM
  yourtablename
GROUP BY
  make
ORDER BY FIELD(make, 'Dodge','Toyota','Honda',make),
  total DESC

UNION ALL

SELECT
  'Total Cars',
  COUNT(*)
FROM
  yourtable

when you ran that you got an error message about the use of the order by clause right?

All we have to do is make that first query a derived table and select everything from it by wrapping it in another SELECT statement like this:

SELECT * FROM -- one of the few times using * is okay
(
  SELECT
    make,
    COUNT(*) as total
  FROM
    yourtablename
  GROUP BY
    make
  ORDER BY FIELD(make, 'Dodge','Toyota','Honda',make),
    total DESC
) as derivedtablenamehere

UNION ALL

SELECT
  'Total Cars',
  COUNT(*)
FROM
  yourtable

you now have all your cars totaled up, with specific makes at the top irrespective of count, and then used a separate query for the total (not necessary if you don't need an order by statement in the first query).



Edited 1 time(s). Last edit at 02/12/2009 09:15PM by Guelphdad.