How to compare column values in SQL grouped by one column?

I am practicing SQL from a cars database and I have the following CAR table/view and I need to select the brand which sells more cars than engines/motorcycles. This means that I have to select the rows where only UNIT_SOLD for CAR type is greater than the value for ENGINE OR MOTORCYCLE.

BRAND  UNIT_SOLD    TYPE
ACURA   1     MOTORCYCLE
CHEVY   3     ENGINE
CHEVY   1     CAR
DODGE   1     ENGINE
FORD    1     MOTORCYCLE
FORD    1     ENGINE
FORD    3     CAR
NISSAN  1     MOTORCYLE
NISSAN  1     ENGINE
NISSAN  2     CAR
TOYOTA  1     MOTORCYCLE
TOYOTA  3     CAR
VOLVO   3     MOTORCYCLE
VW  2     CAR

For example, in the previous example I have to return:

FORD
NISSAN
TOYOTA
VW

explanation: the previous brands sells more cars than engine/motorcycle. CHEVY is not considered due that they sells 3 engine and only 1 car.

I have been trying to approach the problem with conditional SELECT statements, however I don not know if that is a good approach.

Thanks so much.

SELECT BRAND
 FROM CARS A
WHERE A.TYPE = 'CAR'
 AND A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'ENGINE' AND A.MAKER=B.MAKER)
 OR  A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'MOTORCYCLE' AND A.BRAND=B.BRAND);

SELECT BRAND
 FROM CARS A
WHERE A.TYPE = 'CAR'
 AND A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'ENGINE' AND A.MAKER=B.MAKER)
 OR  A.UNIT_SELLS > (SELECT B.UNIT_SELLS FROM CARS B WHERE B.TYPE = 'MOTORCYCLE' AND A.BRAND=B.BRAND);

Currently I am able to get the rows where CAR unit_sold ar greater, however still I am not able to get those rows where any number of cars are sold and they do not have any motorcycle/engine sold.

Answer

Using conditional aggregation you can get each total and compare.

SQL DEMO

 SELECT brand,
        -- just for debug
        SUM(CASE WHEN TYPE = 'CAR'    THEN UNIT_SOLD ELSE 0 END) as total_cars,
        SUM(CASE WHEN TYPE = 'ENGINE' THEN UNIT_SOLD ELSE 0 END) as total_engines,
        SUM(CASE WHEN TYPE = 'MOTORCYCLE' THEN UNIT_SOLD ELSE 0 END) as total_motorcycles
 FROM cars
 GROUP BY brand
 HAVING SUM(CASE WHEN TYPE = 'CAR'    THEN UNIT_SOLD ELSE 0 END) >
        SUM(CASE WHEN TYPE = 'ENGINE' THEN UNIT_SOLD ELSE 0 END) 

    AND SUM(CASE WHEN TYPE = 'CAR'        THEN UNIT_SOLD ELSE 0 END) >
        SUM(CASE WHEN TYPE = 'MOTORCYCLE' THEN UNIT_SOLD ELSE 0 END) 

OUTPUT

|  brand | total_cars | total_engines | total_motorcycles |
|--------|------------|---------------|-------------------|
|   FORD |          3 |             1 |                 1 |
| NISSAN |          2 |             1 |                 0 |
| TOYOTA |          3 |             0 |                 1 |
|     VW |          2 |             0 |                 0 |