Geek Answers Handbook

Sql query to find the sum of all rows and the number of duplicates

If the 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 write a query to sum the QTotal column for all rows and find the number of repeating rows for the Tdatetime column.

The result will look like this:
 

  Year   Total  Count

     97 | 1340 | 4
The third column as a result does not include the number of individual rows in the table. And the result is grouped by year in the TDateTime column.
+5
source share
6 answers

The following query might 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
0
source

This will work if you really want to group 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 year in the tDateTime column. Is it correct?

, :

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

SELECT GROUPing QTotal, COUNT (subSELECT WHERE QTotal - ). , SQL, .

0

- :

select Year(Tdatetime)  ,sum(QTotal), count(1) from table group by year(Tdatetime )


select Tdatetime  ,sum(QTotal), count(1) from table group by year(Tdatetime)

(:))

select 'Year ' + cast(Year(tdatetime) as varchar(4)) 
     + '|' + cast(sum(QTotal) as varchar(31)) 
     + '|' + cast(count(1) as varchar(31)) 
 from table group by year(Tdatetime )

? ? ?

0
SELECT
 YEar + year(Tdatetime),
 SUM ( QTotal ),
 (SELECT COUNT(*) FROM ( 
 SELECT Tdatetime FROM tDateTime GROUP BY Tdatetime        
 HAVING COUNT(QID) > 1) C
FROM
 Tdatetime t

GROUP BY 
 YEar + year(Tdatetime)
0

, stackoverflow. , . , .

To answer the OMG Ponies question, this is a SQL Server 2008 database. @Abe Miessler, line with SID 120 does not contain duplicates. the first row for SID 120 shows 9:00 AM in the datetime column, and the second row shows 10:00 AM.

@Zafer, your request is an accepted answer. I did some tricks to make it work. Thank you Thanks to Abe Miessler and others for your help.

0
source

More articles:


All Articles