CODEWITHSUNDEEP
sqlsql-serverreporting-servicesodbc
great77
great77

Reputation: 143

Using Count with Duplicate date and time

I have a table that has duplicate date and time, Some date has duplicate time but really want to count it as 1 using SQL query, the distinct is not working and also not sure how to do this any help ? The purpose is to calculate how much download for each register number for each day. eg 2018-01-08 has duplicate Starttime, and should be counted as 1.

My query is not doing that.

Select  StartDate, Starttime, count(*) AS TOTALDOWNLOAD, RegisterNumber 
FROM            `SAMPLE.csv`
WHERE        (MaintenanceFlightTime > 0)
group by StartDate,Starttime, RegisterNumber 

RegisterNumber  StartDate   StartTime
    A         2017-11-27    19:22:17
    A         2017-11-27    19:45:39
    B         2017-11-29    14:09:30
    A         2017-11-28    21:51:38
    A         2017-11-30    17:09:34
    B         2017-12-01    15:19:35
    A         2017-12-01    19:31:42
    B         2017-12-02    14:59:28
    B         2017-12-03    17:38:39
    B         2017-12-03    19:09:19
    B         2017-12-04    10:16:24
    B         2017-12-04    13:43:37
    B         2017-12-05    06:37:22
    A         2017-12-05    10:24:37
    A         2017-12-05    14:40:19
    B         2017-12-05    19:44:06
    A         2017-12-06    08:26:55
    A         2017-12-06    06:30:38
    A         2017-12-06    11:35:41
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52
    A         2018-01-08    06:40:52

Upvotes: 0

Views: 98

Answers (2)

Dataichou
Dataichou

Reputation: 317

try this:

    Select  RegisterNumber, 
            count(distinct StartDate || '-' || Starttime) AS TOTALDOWNLOAD
    FROM            `SAMPLE.csv`
    WHERE        (MaintenanceFlightTime > 0)
    GROUP BY RegisterNumber

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You should only include in the group by the rows you want in the result set. So, if you want to count days per RegisterNumber, you would use:

SELECT RegisterNumber, COUNT(DISTINCT StartDate)
FROM `SAMPLE.csv`
WHERE MaintenanceFlightTime > 0
GROUP BY RegisterNumber ;

If you want to count distinct combinations of the date and time, then some databases let you use COUNT(DISTINCT) with multiple arguments:

SELECT RegisterNumber, COUNT(DISTINCT StartDate, StartTime)
FROM `SAMPLE.csv`
WHERE MaintenanceFlightTime > 0
GROUP BY RegisterNumber ;

In others, you would need to use string concatenation or date/time functions to combine the values into a single column. At the extreme, you can also use a subquery:

SELECT RegisterNumber, COUNT(*)
FROM (SELECT DISTINCT RegisterNumber, StartDate, StartTime
      FROM `SAMPLE.csv`
      WHERE MaintenanceFlightTime > 0
     ) s
GROUP BY RegisterNumber ;

Upvotes: 0

Related Questions