SQL query count WHERE result of count greater than 0-CodePudding

I have two tables:

vehicles_directory:

| id |       model | year |
|  1 | Lamborghini | 2021 |
|  2 |    Mercedes | 2019 |
|  3 |  VolksWagen | 2015 |

cars_showroom:

| id |       color |  price | date_of_sale | model_id |
|  1 |         Red |  89900 |   2022-01-01 |        1 |
|  2 |       Green |  24000 |         NULL |        2 |
|  3 |        Black|  13900 |         NULL |        3 |
|  4 |       White |  89900 |   2022-01-02 |        1 |
|  5 |        Pink |  24000 |   2021-01-01 |        2 |
|  6 |        Grey |  13900 |   2020-01-01 |        3 |
|  7 |      Yellow |  89900 |   2022-01-03 |        1 |
|  8 |        Blue |  24000 |   2021-01-01 |        2 |
|  9 |       Brown |  13900 |         NULL |        3 |

So i need a sql query to find car models that are currrenty unsold and count them, the response should belike this:

|       model | unsold|
|    Mercedes |     1 |
|  Volkswagen |     2 |

I have tried to wrote this query like this:

SELECT 
   vehicles_directory.model, 
   (COUNT(*) - COUNT(cars_showroom.date_of_sale)) AS unsold
FROM vehicles_directory 
LEFT JOIN cars_showroom ON vehicles_directory.id = cars_showroom.model_id
GROUP BY vehicles_directory.model
HAVING (COUNT(*) - COUNT(cars_showroom.date_of_sale)) > 0;

but the last line throws error, without

HAVING (COUNT(*) - COUNT(cars_showroom.date_of_sale)) > 0;

everything works fine except the the unsold that equals 0 shows either like:

|       model | unsold|
| Lamborghini |     0 |
|    Mercedes |     1 |
|  Volkswagen |     2 |

i do not need this undsold that equals 0

CodePudding user response：

I think this should do it:

SELECT vd.model as model, count(1) as unsold
FROM cars_showroom cs
INNER JOIN vehicles_directory vd ON vd.id = cs.model_id
WHERE cd.date_of_sale IS NULL
GROUP BY vd.id;