MySQL select query with case then and count-CodePudding

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

CodePudding user response：

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'

CodePudding user response：

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

CodePudding user response：

You could use CTE to find count first.

Try like this

;WITH couponscount AS(
        SELECT campaign_id, code, COUNT(campaign_id) cnt
    FROM Coupons 
    GROUP BY campaign_id, code, 
)
SELECT *,
       CASE WHEN cc.cnt > 1 then 'MULTIPLE' 
       ELSE cc.code END
FROM   campaigns c
LEFT JOIN couponscount cc ON c.id = cc.campaign_id

Please Note - This is just sample script. Not tested fully