GROUP BY With SUM - Results double

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY With SUM - Results double

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 13:31:35

I have this query below. Everything is OK except the results of the SUM are double. I think I narrowed it down to the JOIN. I have commented out the working (double) line with the --. When I try to run it with the present code, the results are wrong. I think it is the subquery. Please help me get it right.

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SELECT @StartDate=('07/27/2009')
SELECT @EndDate=('07/28/2009')
SELECT     O.OPERATOR_NAME
	, W.STATION_NAME
	, S.START_DATE_TIME
	, S.END_DATE_TIME
	, S.SYNC_MODE
	, S.SYNCH_TYPE
	, COUNT(DISTINCT CASE WHEN D.RECORDS_UP > 0 THEN D.PATIENT_ID END) PTS_UP
	, COUNT(DISTINCT CASE WHEN D.RECORDS_DOWN > 0 THEN D.PATIENT_ID END) PTS_DN
	, SUM(D.RECORDS_UP) RECS_UP
	, SUM(D.RECORDS_DOWN) RECS_DOWN
	, COUNT(DISTINCT C.CONFLICT_ID) CONF
FROM         SYS_SYNCH_LOG S
--INNER JOIN  SYS_SYNCH_LOG_DETAIL D ON S.SYNCH_LOG_ID = D.SYNCH_LOG_ID 

INNER JOIN
(
    SELECT	PATIENT_ID
	, SYNCH_LOG_ID
	, RECORDS_UP
	, RECORDS_DOWN
	, COUNT(*) AS PT_COUNT
	, SUM(RECORDS_UP) AS RECS_UP
	, SUM(RECORDS_DOWN) AS RECS_DOWN
    FROM 
      	SYS_SYNCH_LOG_DETAIL
    GROUP BY 
    PATIENT_ID, SYNCH_LOG_ID, RECORDS_UP, RECORDS_DOWN
) D
ON  S.SYNCH_LOG_ID = D.SYNCH_LOG_ID

INNER JOIN  O_OPERATOR O ON S.OPERATOR_ID = O.OPERATOR_ID 
INNER JOIN  O_WORKSTATION W ON S.STATION_ID = W.STATION_ID 
INNER JOIN  SYS_SYNCH_CONFLICT C ON S.SYNCH_LOG_ID = C.SYNCH_ID
WHERE     (S.START_DATE_TIME >= @StartDate) AND (S.END_DATE_TIME < = @EndDate + 1)
GROUP BY O.OPERATOR_NAME
	, W.STATION_NAME
	, S.START_DATE_TIME
	, S.END_DATE_TIME
	, S.SYNC_MODE
	, S.SYNCH_TYPE

I have written and re-written the sub-query over and over again and got nothing but syntax errors or bindinb errors, etc. At least now it runs, but I am not getting correct results. The inner join statement with the subquery is the problem, as when it is commented out and the other one above commented back in, it works, but with double results. I don't mean to be repetitive, but I want to make sure I am understood. Thank you in advance for your help.

Duane

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:26:43

seems like this


DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SELECT @StartDate=('07/27/2009')
SELECT @EndDate=('07/28/2009')
SELECT     O.OPERATOR_NAME
	, W.STATION_NAME
	, S.START_DATE_TIME
	, S.END_DATE_TIME
	, S.SYNC_MODE
	, S.SYNCH_TYPE
	, COUNT(DISTINCT CASE WHEN D.RECORDS_UP > 0 THEN D.PATIENT_ID END) PTS_UP
	, COUNT(DISTINCT CASE WHEN D.RECORDS_DOWN > 0 THEN D.PATIENT_ID END) PTS_DN
	, RECS_UP
,RECS_DOWN	, COUNT(DISTINCT C.CONFLICT_ID) CONF
FROM         SYS_SYNCH_LOG S
--INNER JOIN  SYS_SYNCH_LOG_DETAIL D ON S.SYNCH_LOG_ID = D.SYNCH_LOG_ID 

INNER JOIN
(
    SELECT	PATIENT_ID
	, SYNCH_LOG_ID
	, RECORDS_UP
	, RECORDS_DOWN
	, COUNT(*) AS PT_COUNT
	, SUM(RECORDS_UP) AS RECS_UP
	, SUM(RECORDS_DOWN) AS RECS_DOWN
    FROM 
      	SYS_SYNCH_LOG_DETAIL
    GROUP BY 
    PATIENT_ID, SYNCH_LOG_ID, RECORDS_UP, RECORDS_DOWN
) D
ON  S.SYNCH_LOG_ID = D.SYNCH_LOG_ID

INNER JOIN  O_OPERATOR O ON S.OPERATOR_ID = O.OPERATOR_ID 
INNER JOIN  O_WORKSTATION W ON S.STATION_ID = W.STATION_ID 
INNER JOIN  SYS_SYNCH_CONFLICT C ON S.SYNCH_LOG_ID = C.SYNCH_ID
WHERE     (S.START_DATE_TIME >= @StartDate) AND (S.END_DATE_TIME < = @EndDate + 1)
GROUP BY O.OPERATOR_NAME
	, W.STATION_NAME
	, S.START_DATE_TIME
	, S.END_DATE_TIME
	, S.SYNC_MODE
	, S.SYNCH_TYPE
,RECS_UP
,RECS_DOWN

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 14:53:09

Thank you for the response - the figures I got at first, as I said, were double. The figures I got with your code are the same that I got when I tried similar things. They are just wrong. I went back to my old query and they are still double. I wanted to make sure the underlying data didn't change. I hope we can still get it. It seems I am so close.

Duane

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-30 : 15:31:04

This query brings back the results following:
SELECT SYNCH_LOG_ID, COUNT(*) AS PT_COUNT, SUM(RECORDS_UP) AS RECS_UP, SUM(RECORDS_DOWN) AS RECS_DOWN
FROM SYS_SYNCH_LOG_DETAIL WHERE SYNCH_LOG_ID = 255972
GROUP BY SYNCH_LOG_ID;
That Synch_Log_ID is the one we are comparing in the main query. The results are:

SYNCH_LOG_ID PT_COUNT RECS_UP RECS_DOWN
255972 280 171 2344

My first query returned the following results:
SYNCH_LOG_ID PT_COUNT RECS_UP RECS_DOWN
255972 280 342 4688

The query you came up with returned these results:
SYNCH_LOG_ID PT_COUNT RECS_UP RECS_DOWN
255972 280 272 4236

I even filtered the query results in the editor to make sure there wasn't more than one 255972 and there was not.

I hope we can narrow this down further, but obviously I am missing something. Thank you again.

Duane

jeremygiaco
Starting Member

14 Posts

Posted - 2009-07-30 : 16:52:42

What does this give you...its seems strange that you are grouping by records_up and records_down in the subquery d, but also selecting the sum of records_up and records_down in the same subquery. I guess its a bit difficult to tell without the data to test with..

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SELECT @StartDate=('07/27/2009')
SELECT @EndDate=('07/28/2009')
SELECT O.OPERATOR_NAME
	,W.STATION_NAME
	,S.START_DATE_TIME
	,S.END_DATE_TIME
	,S.SYNC_MODE
	,S.SYNCH_TYPE
	,COUNT(DISTINCT CASE WHEN SUM(D.RECORDS_UP) > 0 THEN D.PATIENT_ID END) PTS_UP
	,COUNT(DISTINCT CASE WHEN SUM(D.RECORDS_DOWN) > 0 THEN D.PATIENT_ID END) PTS_DN
	,SUM(D.RECORDS_UP) RECS_UP
	,SUM(D.RECORDS_DOWN) RECS_DOWN
	,COUNT(DISTINCT C.CONFLICT_ID) CONF
FROM SYS_SYNCH_LOG S
	INNER JOIN SYS_SYNCH_LOG_DETAIL D ON S.SYNCH_LOG_ID = D.SYNCH_LOG_ID
	INNER JOIN O_OPERATOR O ON S.OPERATOR_ID = O.OPERATOR_ID 
	INNER JOIN O_WORKSTATION W ON S.STATION_ID = W.STATION_ID 
	INNER JOIN SYS_SYNCH_CONFLICT C ON S.SYNCH_LOG_ID = C.SYNCH_ID
WHERE (S.START_DATE_TIME >= @StartDate) 
	AND (S.END_DATE_TIME <= @EndDate + 1)
GROUP BY O.OPERATOR_NAME
	,W.STATION_NAME
	,S.START_DATE_TIME
	,S.END_DATE_TIME
	,S.SYNC_MODE
	,S.SYNCH_TYPE

Jeremy Giaco

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources