CODEWITHSUNDEEP
sqlsql-server
great77
great77

Reputation: 143

SQL COUNT DISTINCT NOT RIGHT OUTPUT

I have a table that I need to count number of download for each day for each aircraft serial. The index Num. has duplicate, I wrote this code, yet not giving right count of each download. Please see sample of the table and also the code below :

SELECT        Startdate, COUNT(*) AS TOTALDOWNLOAD, [HC SERIAL CODE]
FROM            (SELECT   DISTINCT [Index Num#], [HC SERIAL CODE], Format([Date time], 'yyyy-MM-dd') AS Startdate
                          FROM           combinedKPI.csv) a
WHERE        Startdate BETWEEN DATE () AND DATE () - 111
GROUP BY  Startdate,[HC SERIAL CODE]
ORDER BY    Startdate 

HC Serial Index Num.  Date Time
        AAA08   193452  2018-03-15 10:17
        AAA08   193453  2018-03-15 10:17
        AAA08   193454  2018-03-15 10:54
        AAA08   193455  2018-03-15 10:55
        AAA08   193457  2018-03-15 12:03
        AAA08   193458  2018-03-15 12:03
        AAA08   193823  2018-03-15 12:57
        AAA08   193824  2018-03-15 12:57
        AAA08   193826  2018-03-15 13:57
        AAA08   193827  2018-03-15 13:57
        AAA08   194023  2018-03-15 14:58
        AAA08   194024  2018-03-15 14:58
        AAA08   194025  2018-03-15 15:37
        AAA08   194026  2018-03-15 15:38
        AAA08   194030  2018-03-15 16:45
        AAA08   194031  2018-03-15 16:45
        AAA08   194032  2018-03-15 17:16
        AAA08   194033  2018-03-15 17:16
        AAA08   194628  2018-03-16 06:14
        AAA08   194629  2018-03-16 06:14
        AAA08   194630  2018-03-16 08:38
        AAA08   194631  2018-03-16 08:38
        AAA08   194632  2018-03-16 09:23
        AAA08   194633  2018-03-16 09:24
        AAA08   194635  2018-03-16 10:20
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330793  2018-06-19 16:39
        BBB30   330792  2018-06-19 16:03

Upvotes: 0

Views: 68

Answers (2)

sticky bit
sticky bit

Reputation: 37472

It's hard for me to understand how your environment looks like, so there's some guessing involved. I assume [hc serial code] is an aircraft designation.

If you want to have the count per aircraft and day, group by the aircraft designation and the day portion of the date time.

SELECT format([date time], 'yyyy-MM-dd') [startdate],
       count(*) [totaldownload],
       [hc serial code],
       min([Index Num#]) [Index Num#]
       FROM combinedKPI.csv
       WHERE [date time] BETWEEN date() AND date() - 111
       GROUP BY [hc serial code],
                format([date time], 'yyyy-MM-dd')
       ORDER BY format([date time], 'yyyy-MM-dd');

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I suspect you just want to leave the index number out of the subquery:

SELECT Startdate, COUNT(*) AS TOTALDOWNLOAD, [HC SERIAL CODE]
FROM (SELECT DISTINCT [HC SERIAL CODE], Format([Date time], 'yyyy-MM-dd') AS Startdate
      FROM combinedKPI.csv
     ) a
WHERE Startdate BETWEEN DATE () AND DATE () - 111
GROUP BY Startdate, [HC SERIAL CODE]
ORDER BY Startdate ;

EDIT:

Based on your comment, you might just want GROUP BY with no DISTINCT:

SELECT Format([Date time], 'yyyy-MM-dd'), [HC SERIAL CODE], COUNT(*) AS TOTALDOWNLOAD
FROM combinedKPI.csv
WHERE Startdate BETWEEN DATE () AND DATE () - 111
GROUP BY Format([Date time], 'yyyy-MM-dd'), [HC SERIAL CODE]
ORDER BY Format([Date time], 'yyyy-MM-dd') ;

Upvotes: 2

Related Questions