Counting Instances in a table.

Pheonix · March 15th, 2006, 09:50 PM

Say I have a table something like this

Code:

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

And I want to get these results

Code:

|---|---|
| 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 >.<

Pheonix · March 15th, 2006, 10:40 PM

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

Code:

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

grahame · March 17th, 2006, 11:16 PM

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!

Quote:

What makes a good signature?
That is what I would like to know.

<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

Pheonix · March 18th, 2006, 03:25 AM

Quote:

Originally Posted by grahame

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 understand what you mean. Just use the ID column instead of all columns? what purpose? to increase speed?

grahame · March 18th, 2006, 05:25 AM

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

Pheonix · March 18th, 2006, 01:33 PM

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.

sypher · March 18th, 2006, 06:16 PM

Argh online rts games