CODEWITHSUNDEEP
mysql
Kalesh Kaladharan
Kalesh Kaladharan

Reputation: 1058

MySQL select query with case then and count

I have two tables naming campaigns and coupons with the following structure

Campaigns

id name
1 Campaign 1
2 Multiple Coupon Campaign

Coupons

id campaign_id code
1 1 FREDEL
2 2 123-xyz-coupon
3 2 xyw92b-cou

I am trying to get result data set like this

id name reference
1 Campaign 1 FREDEL
2 Multiple Coupon Campaign MULTIPLE

Basically, if a campaign has multiple coupons, it should return MULTIPLE in the reference column, otherwise the coupon code itself.

I've tried something like this

select *, (select CASE WHEN (SELECT count(*) from coupons as temp where temp.id=coupons.id)>1 THEN 'MULTIPLE' ELSE code END AS reference FROM coupons where coupons.campaign_id=campaigns.id limit 1) as reference from campaigns

but the result is not as expected and it shows the first coupon code of both campaigns instead of MULTIPLE for the second one.

Tried the below query as well

select *, (select CASE WHEN count(*)>1 THEN 'MULTIPLE' ELSE coupons.code END FROM coupons where coupons.campaign_id=campaigns.id) as reference from campaigns

and it throws

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db.coupons.code'; this is incompatible with sql_mode=only_full_group_by

NOTE: I am looking for a sub-query on select and to avoid join clause

Thank you

Upvotes: 1

Views: 72

Answers (2)

Varsha Prabhakar
Varsha Prabhakar

Reputation: 316

SELECT
name,
(
    SELECT
        CASE WHEN count(*) > 1 THEN
            'MULTIPLE'
        ELSE
            MIN(code)
        END AS reference
    FROM
        coupons
    WHERE
        coupons.campaign_id = campaigns.id
    GROUP BY
        campaign_id) AS reference
FROM
    campaigns

Upvotes: 1

Akina
Akina

Reputation: 42632

SELECT Campaigns.id,
       Campaigns.name,
       CASE WHEN COUNT(*) = 1
            THEN MAX(Coupons.code)
            ELSE 'MULTIPLE'
            END reference
FROM Campaigns
JOIN Coupons ON Coupons.campaign_id = Campaigns.id
GROUP BY Campaigns.id, Campaigns.name

I was looking for a sub-query select version. – Kalesh Kaladharan

SELECT Campaigns.id,
       Campaigns.name,
       CASE ( SELECT COUNT(*) 
              FROM Coupons 
              WHERE Coupons.campaign_id = Campaigns.id ) 
            WHEN 0 THEN 'NONE'
            WHEN 1 THEN ( SELECT Coupons.code
                          FROM Coupons 
                          WHERE Coupons.campaign_id = Campaigns.id )
            ELSE 'MULTIPLE'
            END reference
FROM Campaigns
-- HAVING reference <> 'NONE'

Upvotes: 4

Related Questions