How to display only results matching a certain condition in SQL?

I would appreciate your help very much as I’m still at beginner level in SQL. Thank you 🙂

I have tables representing counties, vendors and their respective sales of stationery products. I have to display only the counties where the sum of all sales exceeds 100 without counting vendor 2 (Randall). For these counties I need county name, vendor name and sales.

These are my tables (shortened):

Counties

county_key	county_name
1	Custer
2	Kimball
3	Douglas
4	Lancaster

Vendors

vendor_key	vendor_name
1	Highsmith
2	Randall
3	Jackson
4	Herford

Sales

county_key	vendor_key	sales
1	1	50
1	2	70
2	2	20
2	3	30
2	4	80
3	1	50
3	4	20
4	1	10
4	2	30
4	3	90
4	4	20

So in this case only counties 2 and 4 should be displayed (because county 1 sales are above 100 in total, but only with the contribution of vendor 2 Randall which should not be part; county 3 sales are below 100 anyway). Therefore, the result should be as follows:

county_name	vendor_name	sales_wo_randall
Kimball	Jackson	30
Kimball	Herford	80
Lancaster	Highsmith	30
Lancaster	Jackson	90
Lancaster	Herford	20

I worked out this code, but I didn’t manage to include the sales > 100 condition:

WITH sales_wo_randall AS (
        SELECT county_key FROM Counties GROUP BY county_key HAVING SUM(sales) > 100
    )
SELECT co.county_name, ve.vendor_name, sa.sales
FROM Sales sa
JOIN Counties co
ON co.county_key = sa.county_key
JOIN Vendors ve
ON ve.vendor_key = sa.vendor_key
WHERE sa.date = '2022-01-01'
AND ve.vendor_name <> 'Randall'
GROUP BY co.county_name, ve.vendor_name, sa.sales
ORDER BY co.county_name

2nd try: I tried to manage to include the sales > 100 condition, but then, I am not able to include my desired columns county_name and vendor_name as they would need to be included in GROUP BY clause which in turn unfortunately leads to getting just certain rows with sales > 100, not as a sum … Here is the “correct” query but without my desired columns, as I stated:

SELECT
    sa.county_key,
    SUM(sa.sales)
FROM
    Sales sa
WHERE sa.date = '2022-01-01' AND sa.vendor_key <> '2'
GROUP BY
    sa.county_key
HAVING SUM(sa.sales) > 100

Thank you in advance!

Answer

Finally, I managed it 🙂

Here is my answer if anybody has to struggle with the same problem:

WITH sales_wo_randall_greater_100 AS
(SELECT
    sa.county_key,
    SUM(sa.sales)
FROM
    Sales sa
WHERE sa.date = '2022-01-01' AND sa.vendor_key <> '2'
GROUP BY
    sa.county_key
HAVING SUM(sa.sales) > 100)

SELECT co.county_name, ve.vendor_name, sa.sales
FROM Sales sa
JOIN Counties co
ON co.county_key = sa.county_key
JOIN Vendors ve
ON ve.vendor_key = sa.vendor_key
WHERE sa.date = '2022-01-01'
AND ve.vendor_name <> 'Randall'
AND co.county_key IN (SELECT county_key FROM sales_wo_randall_greater_100)
GROUP BY co.county_name, ve.vendor_name, sa.sales
ORDER BY co.county_name

Advertisement

Answer