Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql query to find sum of all rows and count of duplicates

If data is in the following format:

SID  TID  Tdatetime        QID   QTotal  
----------------------------------------
100  1    01/12/97 9:00AM  66    110   
100  1    01/12/97 9:00AM  66    110  
100  1    01/12/97 10:00AM 67    110  
100  2    01/19/97 9:00AM  66    .  
100  2    01/19/97 9:00AM  66    110  
100  2    01/19/97 10:00AM 66    110  
100  3    01/26/97 9:00AM  68    120  
100  3    01/26/97 9:00AM  68    120  
110  1    02/03/97 10:00AM 68    110  
110  3    02/12/97 9:00AM  64    115  
110  3    02/12/97 9:00AM  64    115  
120  1    04/05/97 9:00AM  66    105  
120  1    04/05/97 10:00AM 66    105  

I would like to be able to write a query to sum the QTotal column for all rows and find the count of duplicate rows for the Tdatetime column.

The output would look like:

  Year   Total  Count
97 | 1340 | 4
The third column in the result does not include the count of distinct rows in the table. And the output is grouped by the year in the TDateTime column.
like image 336
ARK Avatar asked Nov 05 '22 09:11

ARK


2 Answers

The following query may help:

SELECT 
    'YEAR ' + CAST(sub.theYear AS VARCHAR(4)), 
    COUNT(sub.C), 
    (SELECT SUM(QTotal) FROM MyTable WHERE YEAR(Tdatetime) = sub.theYear) AS total
FROM 
   (SELECT 
        YEAR(Tdatetime) AS theYear, 
        COUNT(Tdatetime) AS C 
    FROM MyTable 
    GROUP BY Tdatetime, YEAR(Tdatetime)
    HAVING COUNT(Tdatetime) >= 2) AS sub
like image 159
Zafer Avatar answered Nov 09 '22 23:11

Zafer


This will work if you really want to group by the tDateTime column:

SELECT DISTINCT tDateTime, SUM(QTotal), Count(distinct tDateTime)
FROM Table
GROUP BY tDateTime
HAVING  Count(distinct tDateTime) > 1

But your results look like you want to group by the Year in the tDateTime column. Is this correct?

If so try this:

SELECT DISTINCT YEAR (tDateTime), SUM(QTotal), Count(distinct tDateTime)
FROM Table
GROUP BY YEAR (tDateTime)
HAVING  Count(distinct tDateTime) > 1
like image 33
Abe Miessler Avatar answered Nov 10 '22 01:11

Abe Miessler