CODEWITHSUNDEEP
sqloracle-database
Афанасий Йобарев
Афанасий Йобарев

Reputation: 1

group by with two condition

I have a table named cars. It has three columns: mark, color, date5.

I need to group that table with cars color and cars that have date after 2005-01-01

select color, count(color), (select color from cars where date5 > date'2005-01-01')
from cars
group by color;

But it counts the third column at all cars.

I needed to get three columns: 1. Color 2. Count of all colors 3. Count of all colors with date after 2005-01-01

The third condition makes me confused.

Upvotes: 0

Views: 130

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

You can use a case statement to produce 1 when a condition is met and zero otherwise and then sum up

select
    color,
    COUNT(*) AS all_cars,
    SUM(CASE WHEN date5 > date'2005-01-01' THEN 1 ELSE 0 END) AS newer_cars
from cars
group by color;

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You are almost there, just you need to use SUM in your query as following:

select color, 
count(color), 
sum(case when date5 > date'2005-01-01' then 1 end)
from cars
group by color

Cheers!!

Upvotes: 0

Grzegorz Smulko
Grzegorz Smulko

Reputation: 2803

Not sure if I understand your question, but if I do, then in the MS SQL Server syntax it could look like:

SELECT c.color, 
       COUNT(*) AS CountOfAllCars, 
       ISNULL(SUM(newerColors.newercolorscount), 0) AS CountOfNewerCars
FROM   cars c 
       LEFT JOIN (SELECT color, 
                         COUNT(*) AS newerColorsCount 
                  FROM   cars 
                  WHERE  date5 > '2005-01-01' 
                  GROUP  BY color) AS newerColors 
              ON newerColors.color = c.color 
GROUP  BY c.color

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

If you want the count of each color for cars after the 1/01/2005 you should use 

select color, count(*)
from cars 
where date5 > TO_DATE('01/01/2005','dd/mon/yyyy')
group by color;

Upvotes: 1

Related Questions