Mysql Conditional Count

Question

I have this assets table:

asset_id      | mediumint(8)
asset_type_id | mediumint(8)
asset_name    | varchar(200)

and a lookup table:

product_id | mediumint(8)
asset_id   | mediumint(8)
sequence   | smallint(3)

Basically, the lookup table can have multiple entries of assets for an item. And you can get the item description in assets table.

The assets is categorized as an image, video, sound, etc based on its asset_type_id. This asset_type_id is comprises of integers 1 to 16.

Now, I want to count the number of images(1), video(2) and sound(3) ONLY for a particular item. Is there anyway I can do this using one SELECT QUERY?

I can't limit my selection to just asset_type_id = 1 or 2 or 3 because I still need the other assets data.

I want it like product_id | asset_count_image | asset_count_video | asset_count_sound — user587197
– user587197, Commented Jan 12, 2012 at 9:12

gbn · Accepted Answer · 2012-01-12 09:20:26Z

4

SELECT 
   l.product_id, t.asset_type_id, COUNT(*)
FROM
   lookup l
   CROSS JOIN
   (SELECT 1 AS asset_type_id UNION ALL SELECT 2 UNION ALL SELECT 3) t
   LEFT JOIN
   assets a ON l.asset_id = a.asset_id AND t.asset_type_id = a.asset_type_id
GROUP BY
   l.product_id, t.asset_type_id;

Edit: as separate columns

SELECT 
   l.product_id, 
   COUNT(CASE WHEN a.asset_type_id = 1 THEN 1 END) AS asset_count_image,
   COUNT(CASE WHEN a.asset_type_id = 2 THEN 1 END) AS asset_count_video,
   COUNT(CASE WHEN a.asset_type_id = 3 THEN 1 END) AS asset_count_sound
FROM
   lookup l
   LEFT JOIN
   assets a1 ON l.asset_id = a.asset_id AND a.asset_type_id IN (1, 2, 3)
GROUP BY
   l.product_id;

edited Jan 12, 2012 at 9:20

answered Jan 12, 2012 at 9:14

gbn

435k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user587197 Over a year ago

HI gbn, sorry I forgot to include. I cannot use group by asset id because the result should be grouped by product_id.

gbn Over a year ago

@Jan-DeanFajardo: I changed that after your comment

Collectives™ on Stack Overflow

Mysql Conditional Count

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related