CODEWITHSUNDEEP
mysqldatabasecount
Moeez
Moeez

Reputation: 478

Get different counts against different conditions for same table

I have already checked and tried this solution but it's not working for me. The simple query is 

SELECT COUNT(ims.`installation_id`) AS 'total_images', COUNT(ims.`image_name`) 
AS 'images_uploaded'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`installation_id` = 1 AND ims.`image_upload_flag` = 1

The output is

enter image description here

In above result, total_images is 2 but actually, it's 4. The output of images_uploaded is correct. But I want to get different results. Below is what I have tried 

SELECT COUNT(ims.`installation_id`) AS 'total_images'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`installation_id` = 1 
GROUP BY ims.`installation_id`
UNION ALL
SELECT COUNT(ims.`id`) AS 'images_uploaded'
FROM `installation_images_site` ims
INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1

The output I am getting now is

enter image description here

The count is ok but I want two separate columns total_images and images_uploaded

How can I achieve that? Any help would be highly appreciated.

Upvotes: 1

Views: 46

Answers (1)

John Woo
John Woo

Reputation: 263843

You can actually simplify your query by using conditional aggregate function

SELECT  SUM(CASE WHEN ims.`installation_id` = 1 THEN 1 ELSE 0 END) AS total_images, 
        SUM(CASE WHEN ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1 THEN 1 ELSE 0 END) AS images_uploaded
FROM    `installation_images_site` ims
        INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
WHERE   (ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1)
        OR (ims.`installation_id` = 1) 
GROUP   BY ims.`installation_id`

or wrapping your query in a subquery which gives the same result

SELECT  MAX(CASE WHEN RN = 1 THEN total_count ELSE 0 END) AS total_images,
        MAX(CASE WHEN RN = 2 THEN total_count ELSE 0 END) AS images_uploaded
FROM
    (
        SELECT COUNT(ims.`installation_id`) AS 'total_count', 1 AS RN
        FROM `installation_images_site` ims
        INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
        WHERE ims.`installation_id` = 1 
        GROUP BY ims.`installation_id`
        UNION ALL
        SELECT COUNT(ims.`id`) AS 'total_count', 2 AS RN
        FROM `installation_images_site` ims
        INNER JOIN `installations` ins ON ins.`id` = ims.`installation_id`
        WHERE ims.`image_upload_flag` = 1 AND ims.`installation_id` = 1
    ) a

Upvotes: 1

Related Questions