moshaye3 moshaye3 - 12 days ago 7
SQL Question

GROUP EACH ROW BASED ON DATING AND COUNT

I have a table already generated, but want to group by counting the TAILNO by date. But I can only get the total download for each day for all tailno instead for each tailno. Can someone help with this, and there is no way to put column TAILNO in the code as well the code is :

Select CAST(FlightDate AS DATE) AS DATING, count(*) AS TOTALDOWNLOAD
from [base].[dbo].[FlightOperations]

WHERE FlightDate >= CONVERT(DATE, dateadd(dd,datediff(dd,0, GetDate()- 7), 0),111)

--- dd means day

GROUP BY CAST(FlightDate AS DATE)


table is :

DATING TAILNO
--------------------------------------
2016-11-11 024
2016-11-14 024
2016-11-14 024
2016-11-14 024
2016-11-15 024
2016-11-15 024
2016-11-16 024
2016-11-16 024
2016-11-16 024
2016-11-16 024
2016-11-16 024
2016-11-16 024
2016-11-17 024
2016-11-17 024
2016-11-17 024
2016-11-17 024
2016-11-11 030
2016-11-12 030
2016-11-12 030
2016-11-12 030
2016-11-12 030
2016-11-13 030
2016-11-13 030
2016-11-15 030
2016-11-15 030
2016-11-15 030
2016-11-15 030
2016-11-15 030
2016-11-16 030
2016-11-16 030
2016-11-16 030
2016-11-16 030
2016-11-16 030
2016-11-17 030
2016-11-17 030
2016-11-18 030
2016-11-11 057
2016-11-11 057
2016-11-11 057
2016-11-14 057
2016-11-14 057
2016-11-14 057
2016-11-15 057
2016-11-15 057
2016-11-15 057
2016-11-16 057
2016-11-16 057
2016-11-16 057
2016-11-17 057
2016-11-17 057
2016-11-17 057
2016-11-11 058
2016-11-11 058
2016-11-14 058
2016-11-14 058
2016-11-14 058
2016-11-15 058
2016-11-15 058
2016-11-17 058
2016-11-17 058
2016-11-11 125
2016-11-11 125
2016-11-11 125
2016-11-12 125
2016-11-13 125
2016-11-14 125
2016-11-14 125
2016-11-14 125
2016-11-15 125
2016-11-15 125
2016-11-15 125
2016-11-16 125
2016-11-17 125
2016-11-17 125
2016-11-14 185
2016-11-14 185
2016-11-15 185
2016-11-15 185
2016-11-15 185
2016-11-15 185
2016-11-16 185
2016-11-16 185
2016-11-16 185
2016-11-17 185
2016-11-17 185
2016-11-17 185
2016-11-17 185
2016-11-11 190
2016-11-13 190
2016-11-13 190
2016-11-13 190
2016-11-14 190
2016-11-14 190
2016-11-14 190
2016-11-14 190
2016-11-15 190
2016-11-15 190
2016-11-15 190
2016-11-16 190
2016-11-16 190
2016-11-17 190
2016-11-17 190
2016-11-17 190
2016-11-18 190
2016-11-11 216
2016-11-11 216
2016-11-14 216
2016-11-14 216
2016-11-14 216
2016-11-14 216
2016-11-15 216
2016-11-15 216
2016-11-15 216
2016-11-16 216
2016-11-17 216
2016-11-17 216
2016-11-11 237
2016-11-11 237
2016-11-11 237
2016-11-14 237
2016-11-15 237
2016-11-15 237
2016-11-15 237
2016-11-16 237
2016-11-16 237
2016-11-16 237
2016-11-17 237
2016-11-17 237
2016-11-18 237
2016-11-11 241
2016-11-11 241
2016-11-14 241
2016-11-14 241
2016-11-14 241
2016-11-15 241
2016-11-15 241
2016-11-15 241
2016-11-15 241
2016-11-15 241
2016-11-17 241
2016-11-17 241
2016-11-17 241
2016-11-17 241
2016-11-18 241
2016-11-18 241
2016-11-18 241
2016-11-11 250
2016-11-11 250
2016-11-12 250
2016-11-14 250
2016-11-14 250
2016-11-14 250
2016-11-15 250
2016-11-15 250
2016-11-15 250
2016-11-16 250
2016-11-16 250
2016-11-16 250
2016-11-16 250
2016-11-17 250



DATING TOTALDOWNLOAD TAIL NO
---------------------------------------------
2016-11-11 1 024
2016-11-14 3 024
2016-11-15 2 024
2016-11-16 6 024
2016-11-17 4 024

2016-11-11 1 030
2016-11-12 4 030
2016-11-13 2 030
2016-11-15 5 030
2016-11-16 5 030
2016-11-17 2 030
2016-11-18 1 030

Answer

Start with this query:

Select CAST(FlightDate AS DATE) AS DATING,
       TailNo, count(*) AS TOTALDOWNLOAD
from [base].[dbo].[FlightOperations]
where FlightDate >= CONVERT(DATE, GetDate() - 7) 
group by CAST(FlightDate AS DATE), TailNo
order by CAST(FlightDate AS DATE)

This will have one row per date and per TailNo. Notice that I also simplified the date logic in the WHERE clause.

Comments