I have a cars database which includes the following columns

Make
Model

I want to query how many cars of how of each make and then how many of each model.

So for example

Honda: 23
Fit: 10
Accord: 10
Civic: 3

BMW: 13
3 Series: 10
i8: 3

I can do it, I have this for my make, which shows results for Honda and BMW correctly but I also want to list the count of each model.

SELECT `type`, `variant`, COUNT(`variant`) as TTL FROM `fleet` WHERE `exited` IS NULL GROUP BY `variant`, `type`

    I'd start with

    SELECT make, variant, count(*) as TTL
FROM fleet
GROUP BY ROLLUP(make, variant)
ORDER BY make, variant

    and note that a result row with a null "variant" represents a total for a "make".

      #1064 - 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 '(type,variant) ORDER BY type, variant LIMIT 0, 25' at line 1

        Oh, I don't use MySQL; you'll have to check the manual to see how it expects rollups to be described.

          "SELECT type, variant, count(*) as TTL FROM fleet GROUP BY type, variant WITH ROLLUP"

          My rows are like

          Honda Accord 2
          Honda Civic 5
          Honda NULL 7

          How do I get that total count for Honda being out? it won't always be the third row

            If you add an order by on the variant column, the null should sort first in MySQL, so you could do ORDER BY type, variant, each type should have the type total as the first row returned. However, I'm thinking it might be easier to just build an array by cycling through the result rows, with an if condition checking if variant is null, and in that case setting the type total instead of adding a type sub-array, ending up with something like this, or whatever makes sense for how you ultimately want to use the data...

            Array
(
    [Honda] => Array
    (
        [total] => 12
        [variant] => Array
        (
            [Accord] => 8
            [Civic] => 4
        )
    )
)

              As @NogDog says, one can enforce a particular order on the result set by using an ORDER BY clause (as I did in my response). Indeed, such a clause is required for any SELECT where the order of results is significant, because it's undefined otherwise.

              Incidentally, I forgot to mention. Congratulations @NZ_Kiwis on your anniversary: you've been a registered member here for fourteen years! That's even longer than @NogDog!

                Write a Reply...