CODEWITHSUNDEEP
sqlsql-serversql-server-2008
CIPHER
CIPHER

Reputation: 237

Count of similar data based on different range of dates

table name - flights in sql server 2008

   date       name
2003/04/29  air_France
2003/04/05  Dale_avia 
2003/04/08  Dale_avia 
2003/04/08  Dale_avia 
2003/04/02  Dale_avia 
2003/04/05  Aeroflot  
2003/04/25  Aeroflot  
2003/04/01  Don_avia  
2003/04/01  Don_avia  
2003/04/01  Don_avia  
2003/04/13  Don_avia  
2003/04/13  Don_avia  
2003/04/13  Don_avia  
2003/04/14  Don_avia  
2003/04/14  Don_avia  
2003/04/01  Don_avia  
2005/11/04  British_AW
2005/11/07  British_AW
2005/11/07  British_AW
2005/11/09  British_AW

Now I tried to filter occurrence of name in particular range of dates based on date column, I mean 3 sets for 4th month 2003 on dates between 1 to 10, 11 to 20, 21 to 30…..But I struggle to frame correct query

I trying to get Like below

name       | 1-10  | 11-20  | 21-30
---------------------------------- 
Don_avia   |   4   |   5    |  0 
Aeroflot   |   1   |   0    |  1

i cant able to proceed in correct way with correct query....

please help me ..thanks in advance !!!!!!

Upvotes: 0

Views: 70

Answers (2)

user4141128
user4141128

Reputation: 11

SELECT DISTINCT name,
(SELECT COUNT(name) FROM flights AS F1 WHERE F.name = F1.name AND DAY(Date) BETWEEN 1 AND 10) AS "1-10", (SELECT COUNT(name) FROM flights AS F2 WHERE F.name = F2.name AND DAY(Date) BETWEEN 11 AND 20) AS "11-20", (SELECT COUNT(name) FROM flights AS F3 WHERE F.name = F3.name AND DAY(Date) BETWEEN 21 AND 30) AS "21-30" FROM flights AS F

Upvotes: 0

Rimas
Rimas

Reputation: 6024

SELECT
  name,  
  SUM(CASE WHEN DAY(date) BETWEEN 1 AND 10 THEN 1 ELSE 0 END) as "1-10",
  SUM(CASE WHEN DAY(date) BETWEEN 11 AND 20 THEN 1 ELSE 0 END) as "11-20",
  SUM(CASE WHEN DAY(date) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) as "21-30"
FROM flights
GROUP BY name

Test it on SQL Fiddle

Upvotes: 1

Related Questions