CODEWITHSUNDEEP
mysqlgroup-by
Makan
Makan

Reputation: 2756

Group by one column and count the other conditionally - Mysql

I have a scheme like the following: (id int primary key, itemid int, title char(3) ) which is a database of online purchases in MySQL. Title column can be either 'Mr' or 'Mrs'. I want to see how many men have purchased each item. for example the following table:

(id, itemid, title)
(1 , 1     , 'Mr' )
(2 , 1     , 'Mr' )
(3 , 2     , 'Mrs')
(4 , 2     , 'Mrs')
(5 , 2     , 'Mrs')

should result into following table:

(itemid, number_of_men_who_purchased)
(1, 2)
(2, 0)

Most importantly, Note that if No men have purchased an item, still an itemid is to be listed, and a zero should be put in front of it. I cannot form a table where this condition holds. But I think it is possible. As you may have noticed, table is not normalized and performance wont be much of an issue here.

Thank you in advance :)

Upvotes: 0

Views: 35

Answers (1)

rabudde
rabudde

Reputation: 7722

Group on itemid and conditionally sum up. title='Mr' returns 1 if true, else 0, so if no men purchased item with ID 2 the result is 0.

SELECT itemid,SUM(title='Mr') AS number_of_men_who_purchased FROM <table> GROUP BY itemid

Upvotes: 1

Related Questions