MySQL - SELECT ... GROUP BY ...

Question

id  - a_id  -  a_type
---------------------------
1      9       Granny Smith
2      9       Pink Lady
---------------------------
3      3       Pink Lady 
4      3       Fuji 
5      3       Granny Smith
---------------------------
6      7       Pink Lady
7      7       Fuji 
8      7       Fuji
9      7       Granny Smith

Ok, assuming we have an Apple sql table like above; my question, is possible to have a result like below, with one query?

[0] => Array
    (
        [0] => a_id: 7
        [1] => Pink Lady: 1
        [2] => Granny Smith: 1
        [3] => Fuji: 2
    )

[1] => Array
    (
        [0] => a_id: 9
        [1] => Granny Smith: 1
        [2] => Pink Lady: 1
        [3] => 
    )
...

Thank's in advance!

PS: my own query is this:

SELECT a_type , a_id , 
COUNT(a_type) AS tot 
FROM #apple 
GROUP by a_id , a_type 
HAVING tot > 0 
ORDER BY a_type DESC

but this don't do what i need! it produce more than one a_id!

Answer 1

SELECT * FROM table group by a_id

using group by statement you can group the data by one or more columns GroupBy Statement

Answer 2

You could create a list like that with a group by:

select a_id, a_type, count(*)
from AppleTable
group by a_id, a_type

Answer 3

SELECT a_id,a_type,COUNT(*) FROM Apple GROUP BY a_id, a_type

This will get you a list of distinct a_id and a_type tuples, but you will still have to parse the list to consolidate those of the same a_id into a single hash table.

Or, if you want to, in one step, aggregate all by a_id, do this

SELECT a_id,GROUP_CONCAT(type_count)
  FROM (SELECT a_id,a_type,CONCAT(a_type,",",COUNT(*)) as type_count
          FROM Apple GROUP BY a_id,a_type) as x
 GROUP BY a_id;

This will get you:

7    Pink Lady,1,Granny Smith,1,Fuji 2
9    Granny Smith,1,Pink Lady,1

Regarding my comment: if you have MySQL this is what you get

Creating the exact table you have, with the exact data, and run my query, I get the following.

mysql> SELECT a_id,GROUP_CONCAT(type_count) FROM (SELECT a_id,a_type,CONCAT(a_type,",",COUNT(*)) as type_count FROM Apple GROUP BY a_id,a_type) as x  GROUP BY a_id;

|    3 | Fuji,1,Granny Smith,1,Pink Lady,1 | 
|    7 | Fuji,2,Granny Smith,1,Pink Lady,1 | 
|    9 | Granny Smith,1,Pink Lady,1        | 

Answer 4

Use the distinct and group by like this

select distinct(a_id), a_type, count(a_type) as total_types
from Apple
group by a_id, a_type;

That way, you're getting the Apple id and it's types as well as how many (id, type) results are there as a group.