Counting Instances in a table.

Say I have a table something like this

Code: Select all

|----|----|
| ID |Type|
|----|----|
| 1  | a  |
|----|----|
| 2  | b  |
|----|----|
| 3  | a  |
|----|----|
| 4  | a  |
|----|----|
| 5  | b  |
|----|----|

And I want to get these results

Code: Select all

|---|---|
| a | b |
|---|---|
| 3 | 2 |
|---|---|

Is there an easy way I can do this with a single mysql query?
I would rather avoid several queries and then having to count each row with php, or doing a single query then counting the whole thing in a loop >.> I searched threw the reference manual and couldn't find anything to help me, but I'm almost dead sure their is a way to do this. Please help >.<

Found a solution that almost gives me what I want. And is close enough that I can work with it

Code: Select all

SELECT `Type` , count(*) FROM `table` GROUP BY `Type`

Your solution is a good one, although I would count(ID) to count the number of non-NULL values in that particular column rather than counting all columns. I don't know of any way to transpose rows and columns in the results, and I do a lot of MySQL stuff!
<rant>
One that users can SEE, rather than one that gets squished by a ******ing advert from Google. Such a signature can help the reader of the thread follow up a little bit more about the questioner and person who answers him so that he can judge the quality and background of the original post and reply, and it gives at least a little something back to the person who gives his time for free to answer, in the form of a short ad.
</rant>
-- Graham
OOooo --- errrrr - looks like it might have seen my rant on this thread

I don't understand what you mean. Just use the ID column instead of all columns? what purpose? to increase speed?

Slight increase in speed perhaps ... but the reason I do it by a particular field in other is that it gives you the flexibility to count only rows which actually have a value in the column specified. If you count a column declared as NOT NULL you get everything, of course.

Example of the extra flexibility

select agent, count(price), from hfs group by agent;

will count up the number of properties for which a price is advertised, by agent. Probably not relevant though, if you're not going to want thie extra flexibility later.

-- Graham

Not realy, but I will keep it in mind if I do need it.
Its for a game I work on, the table contains all units on a map, and I needed to know how many of each type of unit at a specific location there was.

Argh online rts games