'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!



Solution 1:[1]

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

Solution 2:[2]

in your WHERE clause cant you write 'sales > 100'? or sales > '100' That's what you need?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Christof
Solution 2 BenHeb