[RESOLVED] Is it possible to count multiple fields in one query

[zzz]

Can I write a query that will count different items in the same table while running the same query?

For example, how many books, journals and DVDs a member checked out and get a count for each?

Table: checkouts
Member ID: 5
😕

[bradgrafelman]

Don't know what the 'checkouts' table looks like, but it sounds like you'd simply GROUP BY a column describing the 'type' of the item. Thus, you'd get 1 row per type of item the user checked out.

[Roger_Ramjet]

What you are after is a CROSSTAB Query. That only exists in some databases like Access but not in MySQL. However there is a way to simulate one in MySQL:

SELECT user_id, user_name,
SUM(IF(item_type='book', 1,0)) as book_count,
SUM(IF(item_type='journal', 1,0)) as journal_count,
SUM(IF(item_type='dvd', 1,0)) as dvd_count
FROM users INNER JOIN checkout USING(user_id);

Have a google for crosstab queries and pivot tables to see what is possible with different platforms.

[Roger_Ramjet]

I forgot to say that this only simulates a crosstab query because you have to know all the types you want to count in advance. A true crosstab query is dynamic and will create as many columns as there are types etc, which is what makes it so powerfull and useful.

Of course, you could do this as dynamic sql: query for the types and then generate the sql to count them all, preferably in a stored procedure. 😃

Oh, and I forgot the GROUP BY clause - my bad :o. See if you can work it out for yourself.

[zzz]

This is perfect! I just had to add GROUP BY user_id because I did not use JOIN in my case.