Sum of count help - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Sum of count help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-13 : 14:38:54

Please help to rewrite the query to get the output.


DECLARE @MT TABLE 

( 

TNO Int, 

TID Int,

TID2 Int,

Dt datetime 

) 

INSERT INTO @MT (TNO, TID,TID2,dt) 

( 
Select '256' , '5','5','5-4-2007' 
Union All 
Select '257', '5','5','5-4-2007' 
Union All 
Select '258', '6','8','6-4-2007' 
Union All 
Select '259', '7','6','7-4-2007' 
) 

DECLARE @HIFRAP TABLE 

( 

TID Int, 

DSCRPTR VARCHAR(50) 

) 



INSERT INTO @HIFRAP (TID, DSCRPTR) 

( 
Select '5' , 'Test5' 
Union All 
Select '6', 'Test6' 
Union All 
Select '7', 'Test7' 
Union All 
Select '8', 'Test8' 
) 
select DISTINCT B.DSCRPTR as DESCR, Count = CASE WHEN Count IS NULL THEN 0 ELSE Count END
from @HIFRAP B
left outer join
(
	select B.DSCRPTR as DESCR ,Count(A.TID) as count
	from @HIFRAP B
	left outer join @MT A
	on A.TID=B.TID 
	where A.dt between '5-4-2007' and '7-4-2007'
	Group by B.DSCRPTR
) t
on b.DSCRPTR = t.DESCR

 
Output:

Desc   Tidcount   Tid2count   Sumcount(Tid+Tid2count)
------ --------   ---------   ----------------------
Test5	2         2             4
Test6	1         1             2
Test7	1         0             1
Test8	0         1             1

thanks for your help in advance!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 14:49:40

[code]SELECT DscrPtr,
SUM(TID) AS TIDcount,
SUM(TID2) AS TID2count,
SUM(TID + TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
1 AS TID,
0 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
1 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID2 = h.TID
) AS d
GROUP BY DscrPtr
ORDER BY DscrPtr[/code]

Peter Larsson
Helsingborg, Sweden

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-13 : 15:51:24

Thanks peter!!
Your query works fine..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-13 : 15:59:45

I want to modify the query to get the counts based on the dt (from date to date).How do i incorporate in the code..

I tried the with below query its not showing the correct results.

SELECT DscrPtr,
SUM(TID) AS TIDcount,
SUM(TID2) AS TID2count,
SUM(TID + TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
1 AS TID,
0 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
1 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID2 = h.TID
Where m.dt between '5-4-2007' and '5-4-2007'
) AS d

GROUP BY DscrPtr
ORDER BY DscrPtr

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 16:19:22

[code]SELECT d.DscrPtr,
SUM(d.TID) AS TIDcount,
SUM(d.TID2) AS TID2count,
SUM(d.TID + d.TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
1 AS TID,
0 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID = h.TID
WHERE m.DT >= '5-4-2007' -- Assuming mdy time format
AND m.DT < '5-5-2007' -- Assuming mdy time format

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
1 AS TID2
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID2 = h.TID
WHERE m.DT >= '5-4-2007' -- Assuming mdy time format
AND m.DT < '5-5-2007' -- Assuming mdy time format
) AS d
GROUP BY d.DscrPtr
ORDER BY d.DscrPtr[/code]Or[code]SELECT d.DscrPtr,
SUM(d.TID) AS TIDcount,
SUM(d.TID2) AS TID2count,
SUM(d.TID + d.TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
1 AS TID,
0 AS TID2,
m.DT
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
1 AS TID2,
m.DT
FROM @HiFrap AS h
INNER JOIN @MT AS m ON m.TID2 = h.TID
) AS d
WHERE d.DT >= '5-4-2007' -- Assuming mdy time format
AND d.DT < '5-5-2007' -- Assuming mdy time format
GROUP BY d.DscrPtr
ORDER BY d.DscrPtr[/code]

Peter Larsson
Helsingborg, Sweden

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-13 : 17:04:11

Thanks peter!! But query is not displaying all the left join results..

quote:

DECLARE @MT TABLE

(

TNO Int,

TID Int,

TID2 Int,

Dt datetime

)

INSERT INTO @MT (TNO, TID,TID2,dt)

(
Select '256' , '5','5','5-4-2007'
Union All
Select '257', '5','5','5-4-2007'
Union All
Select '258', '6','8','6-4-2007'
Union All
Select '259', '7','6','7-4-2007'
Union All
Select '260', '7','6','7-4-2007'
)

DECLARE @HIFRAP TABLE

(

TID Int,

DSCRPTR VARCHAR(50)

)

INSERT INTO @HIFRAP (TID, DSCRPTR)

(
Select '5' , 'Test5'
Union All
Select '6', 'Test6'
Union All
Select '7', 'Test7'
Union All
Select '8', 'Test8'
)

SELECT d.DscrPtr,
SUM(d.TID) AS TIDcount,
SUM(d.TID2) AS TID2count,
SUM(d.TID + d.TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
1 AS TID,
0 AS TID2,
m.DT
FROM @HiFrap AS h
Left JOIN @MT AS m ON m.TID = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
1 AS TID2,
m.DT
FROM @HiFrap AS h
left JOIN @MT AS m ON m.TID2 = h.TID
) AS d
WHERE d.DT >= '5-4-2007' -- Assuming mdy time format
AND d.DT < '5-5-2007' -- Assuming mdy time format
GROUP BY d.DscrPtr
ORDER BY d.DscrPtr

Expected output:

DscrPtr TIDcount TID2count Sumcount
------- -------- --------- --------
Test5 2 2 4
Test6 0 0 0
Test7 0 0 0
Test8 0 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-13 : 17:52:56

There is no LEFT JOIN. There is a UNION ALL.

Peter Larsson
Helsingborg, Sweden

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-07-13 : 18:29:55

How can i get the expected output..

Expected output:

DscrPtr TIDcount TID2count Sumcount
------- -------- --------- --------
Test5 2 2 4
Test6 0 0 0
Test7 0 0 0
Test8 0 0 0

Thanks for your help in advance!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-14 : 03:40:15

[code]SELECT d.DscrPtr,
SUM(d.TID) AS TIDcount,
SUM(d.TID2) AS TID2count,
SUM(d.TID + d.TID2) AS Sumcount
FROM (
SELECT h.DscrPtr,
CASE -- Assuming mdy time format
WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
ELSE 0
END AS TID,
0 AS TID2
FROM @HiFrap AS h
LEFT JOIN @MT AS m ON m.TID2 = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
CASE -- Assuming mdy time format
WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
ELSE 0
END AS TID2
FROM @HiFrap AS h
LEFT JOIN @MT AS m ON m.TID2 = h.TID
) AS d
GROUP BY d.DscrPtr
ORDER BY d.DscrPtr[/code]

Peter Larsson
Helsingborg, Sweden

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-16 : 12:51:50

[code]
Thanks peter!!!Your query is working perfect..
But when I did want to add another colum count in sum i m not getting the results..Please help me in rewriting the below query...

Thanks for your help in advance!!!!

DECLARE @MT TABLE

(

TNO Int,

TID Int,

TID2 Int,

Dt datetime

)

INSERT INTO @MT (TNO, TID,TID2,TID3,dt)

(
Select '256' , '5','5','5','5-4-2007'
Union All
Select '257', '5','5','5','5-4-2007'
Union All
Select '258', '6','8','6','6-4-2007'
Union All
Select '259', '7','6','7','7-4-2007'
Union All
Select '260', '7','6','5','7-4-2007'
)

DECLARE @HIFRAP TABLE

(

TID Int,

DSCRPTR VARCHAR(50)

)

INSERT INTO @HIFRAP (TID, DSCRPTR)

(
Select '5' , 'Test5'
Union All
Select '6', 'Test6'
Union All
Select '7', 'Test7'
Union All
Select '8', 'Test8'
)

SELECT d.DSCRPTR,
SUM(d.TID) AS TIDcount,
SUM(d.TID2) AS TID2count,
SUM(d.TID3) AS TID32count,
SUM(d.TID + d.TID2 + d.TID3 ) AS Sumcount
FROM (
SELECT h.DscrPtr,
CASE -- Assuming mdy time format

WHEN m.DT >= '5-4-2007' AND m.DT < '7-5-2007' THEN 1
ELSE 0
END AS TID,
0 AS TID2
FROM @HiFrap AS h
LEFT JOIN @MT AS m ON m.TID2 = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
CASE -- Assuming mdy time format
WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
ELSE 0
END AS TID2
FROM @HiFrap AS h
LEFT JOIN @MT AS m ON m.TID2 = h.TID

UNION ALL

SELECT h.DscrPtr,
0 AS TID,
CASE -- Assuming mdy time format
WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
ELSE 0
END AS TID3
FROM @HiFrap AS h
LEFT JOIN @MT AS m ON m.TID3 = h.TID
) AS d
GROUP BY d.DscrPtr
ORDER BY d.DscrPtr
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 13:53:44

Keep track of your column names!!!

SELECT		d.DscrPtr,
		SUM(d.TID) AS TIDcount,
		SUM(d.TID2) AS TID2count,
		SUM(d.TID3) AS TID3count,
		SUM(d.TID + d.TID2 + d.TID3) AS Sumcount
FROM		(
			SELECT		h.DscrPtr,
					CASE -- Assuming mdy time format
						WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
						ELSE 0
					END AS TID,
					0 AS TID2,
					0 AS TID3
			FROM		@HiFrap AS h
			LEFT JOIN	@MT AS m ON m.TID2 = h.TID

			UNION ALL

			SELECT		h.DscrPtr,
					0 AS TID,
					CASE -- Assuming mdy time format
						WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
						ELSE 0
					END AS TID2,
					0 AS TID3
			FROM		@HiFrap AS h
			LEFT JOIN	@MT AS m ON m.TID2 = h.TID

			UNION ALL

			SELECT		h.DscrPtr,
					0 AS TID,
					0 AS TID2,
					CASE -- Assuming mdy time format
						WHEN m.DT >= '5-4-2007' AND m.DT < '5-5-2007' THEN 1
						ELSE 0
					END AS TID3
			FROM		@HiFrap AS h
			LEFT JOIN	@MT AS m ON m.TID3 = h.TID
		) AS d
GROUP BY	d.DscrPtr
ORDER BY	d.DscrPtr

Peter Larsson
Helsingborg, Sweden

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-07-16 : 19:52:34

Thank you very much peter!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources