CODEWITHSUNDEEP
sqloracle-databasejoincount
Jula414
Jula414

Reputation: 9

SQL count quantity of SOLD CARS and count DIFFERENT COLOR

i want to show how many cars where sold and which color they had.

I need to show all car brands that are in table: cars. The total quantity of cars that where sold (see table: car_sales) and how many of these cars have the color red or blue.

It should look like this:

Car        | totalQuantity | Quantity red | Quantity blue
------------------------------------------------------------
BMW        |      3        |      1       |        2
Mercedes   |      1        |      1       |        0
Audi       |      2        |      2       |        0 
Chevrolet  |      0        |      0       |        0
Nissan     |      1        |      0       |        1 
Renault    |      0        |      0       |        0
Peugeot    |      0        |      0       |        0

these are my two tables:

table: cars

  Car_id   | Car_brand         
------------------------
  2356     | BMW        
  2359     | Mercedes   
  2358     | Audi 
  2544     | Chevrolet         
  2152     | Nissan  
  2245     | Renault
  2253     | Peugeot

table: car_sales

  sales_id     | Car_brand | color     | car_id  | sales_date
---------------------------------------------------------------
  45654556     | BMW       |  red      |  2356   | 03.02.2009 
  63654552     | Mercedes  |  red      |   ...   |    ... 
  45654565     | BMW       |  blue     |   ...   |    ...
  41456921     | Audi      |  red      |         |
  36636545     | Nissan    |  blue     |         |
  45654565     | BMW       |  blue     |         |
  41456921     | Audi      |  red      |         |

I Hope you can help me. Have a nice day.

Upvotes: 0

Views: 2389

Answers (1)

user330315
user330315

Reputation:

If the colors are fixed to red and blue the following should work:

select c.brand, 
       count(*) as total_quantity,
       count(case when cs.color = 'red' then 1 end) as quantity_red,
       count(case when cs.color = 'blue' then 1 end) as quantity_blud
from cars c
  join car_sales cs on c.car_id = cs.car_id
group by c.brand;

If you have more colors (but still a fix number) you might want to look into the PIVOT operator (search this site, there is a tag for this).

If you have an unknown number of colors this is going to be messy, as you need dynamic SQL and stored procedures - in that case something like that is better done in a reporting tool (e.g. Excel is pretty good in doing pivot queries)

Upvotes: 1

Related Questions