Display zero value in field when query output is null

Question

How many flashlights did John Gray buy?

This is the result I need to achieve:

So far I have this:

IF NOT (EXISTS (SELECT 
        c.[customerid],
        c.[firstname], 
        c.[lastname],
        cp.[quantity]
    FROM [dbo].[customers] AS c 
    INNER JOIN [dbo].[Customer_Purchases] AS cp
    ON c.[customerid] = cp.[customerid]
    WHERE 
        c.[customerid] = 10101
        AND cp.[item] = 'Flashlight'
    GROUP BY 
        c.[customerid],
        c.[firstname], 
        c.[lastname],
        cp.[quantity]
    )
)BEGIN
    SELECT
        c.[customerid] AS ID,
        c.[firstname] AS First Name, 
        c.[lastname] AS Last Name,
        'Flashlight' AS Item,
        '0' As Quantity
    FROM [dbo].[customers] AS c
    WHERE 
        c.[customerid] = 10101
    GROUP BY 
        c.[customerid],
        c.[firstname], 
        c.[lastname]
END

What is the more practical approach?

I have gone trough several SO threads regarding issues of such (this, this, this and this), but the necessary JOIN seems to be making it rather too complex for me.

Edit 1:

Corrected inconsistencies in the code;
Provided hyperlinks to SO threads.

*" as various Errors emerged while I was trying them"* And what were those errors? — Thom A, Jan 01 '21 at 17:01
@Larnu I suppose the downvote is for the error part. You want me to list them out, or edit the question to revert the downvote? — I. Я. Newb, Jan 01 '21 at 17:05
*"I suppose the downvote is for the error part."* It's not mine, so I don't know I'm afraid. It could be for a number of reasons, including saying you're getting an error and not telling us what that error is. There's a lot of users that don't like it when people say "I look at all sorts of posts on [so]" yet don't cite them too. I can't, however, revert a downvote I didn't cast; only the voter can do that. — Thom A, Jan 01 '21 at 17:15
@Larnu, I have added some corrections the question, as their necessity was pointed out. Could not provide error messages. as they were not collect them at the time. Let me know if there is a way to extract them from a log. — I. Я. Newb, Jan 02 '21 at 14:15

score 1 · Accepted Answer · answered Jan 01 '21 at 16:58

How many flashlights did John Gray buy?

Just use left join:

SELECT c.[customerid], c.[firstname], c.[lastname], 
       COALESCE(SUM(cp.quantity), 0)
FROM dbo.customers c LEFT JOIN
     dbo.Customer_Purchases cp
     ON c.customerid = cp.customerid AND
        cp.item = 'flashlight'
WHERE c.customerid = 10101
GROUP BY c.[customerid], c.[firstname], c.[lastname];

Note: You do not want to aggregate by quantity. Presumably, you want to add up all quantities for flashlights. And the filtering on flashlight (but not on customer) needs to be in the ON clause because the purchases are the second table in the LEFT JOIN.

score 1 · Answer 2 · answered Jan 01 '21 at 17:00

1

SELECT c.[customerid], c.[firstname], c.[lastname],
       sum(case when cp.item = 'flashlight' then cp.quantity else 0 end)
FROM dbo.customers AS c 
LEFT JOIN dbo.Customer_Purchases AS cp ON c.customerid = cp.customerid
WHERE c.customerid = 10101
GROUP BY c.[customerid], c.[firstname], c.[lastname]

answered Jan 01 '21 at 17:00

juergen d

201,996
37
293
362

1

Sorry, @Gordon Linoff was faster. Still I like the approach. – I. Я. Newb Jan 01 '21 at 17:10

Display zero value in field when query output is null

2 Answers2