sum 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 count help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-09-06 : 16:25:58

[code]
Please help to rewrite the query to get the correct output.

DECLARE @MT TABLE

(

TNO Int,

TID Int,

TID2 Int,

TID3 Int,

Dt datetime

)

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

(
Select '256' , '5','7','5','5-4-2007'
Union All
Select '257', '6','5','7','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 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

Desired Ouput:

DscrPtr TIDcount TID2count TID3count Sumcount
------- -------- --------- --------- ---------
Test5 1 1 1 3
Test6 1 0 0 1
Test7 0 1 1 2
Test8 0 0 0 0

[/code]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-06 : 16:44:25

Can you also explain the business logic on how you arrived at those numbers..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-09-06 : 20:14:43

[code]

Table:MT

TNO TID TID2 TID3 dt
---- --- ---- ---- -----------------------
256 5 7 5 2007-05-04 00:00:00.000
257 6 5 7 2007-05-04 00:00:00.000
258 6 8 6 2007-06-04 00:00:00.000
259 7 6 7 2007-07-04 00:00:00.000
260 7 6 null 2007-07-04 00:00:00.000

Table HIFRAP:

TD Dscrpt
-- -------
5 Test5
6 Test6
7 Test7
8 Test8

I want total counts of 5,6,7,8 from three columns TID,TID2,TID3 based on the date condition(filter)

Ex: I want a count based on the date between 2007-05-04 00:00:00.000 and 2007-05-05 00:00:00.000

256 5 7 5 2007-05-04 00:00:00.000
257 6 5 7 2007-05-04 00:00:00.000

I should get the output

TD Sumcount
-------- -------
5(Test5) 3
6(Test6) 1
7(Test7) 2
8(Test8) 0

Please let me know if am not clear.[/code]

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-06 : 22:20:06

DECLARE @MT TABLE (TNO int, TID int, TID2 int, TID3 int, Dt datetime)

INSERT INTO @MT
SELECT 256, 5, 7, 5, '5-4-2007'
UNION ALL SELECT 257, 6, 5, 7, '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
SELECT 5, 'Test5'
UNION ALL SELECT 6, 'Test6'
UNION ALL SELECT 7, 'Test7'
UNION ALL SELECT 8, 'Test8'

DECLARE @D1 datetime, @D2 datetime

SELECT @D1 = '20070504', @D2 = '20070505'

SELECT A.DscrPtr,
(SELECT COUNT(*) FROM @MT Z WHERE Z.TID = A.TID AND Z.Dt BETWEEN @D1 AND @D2) AS TIDCount,
(SELECT COUNT(*) FROM @MT Z WHERE Z.TID2 = A.TID AND Z.Dt BETWEEN @D1 AND @D2) AS TID2Count,
(SELECT COUNT(*) FROM @MT Z WHERE Z.TID3 = A.TID AND Z.Dt BETWEEN @D1 AND @D2) AS TID3Count,
(SELECT COUNT(*) FROM @MT Z WHERE Z.TID = A.TID AND Z.Dt BETWEEN @D1 AND @D2)
+ (SELECT COUNT(*) FROM @MT Z WHERE Z.TID2 = A.TID AND Z.Dt BETWEEN @D1 AND @D2)
+ (SELECT COUNT(*) FROM @MT Z WHERE Z.TID3 = A.TID AND Z.Dt BETWEEN @D1 AND @D2) AS SumCount
FROM @HIFRAP A
ORDER BY A.TID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-07 : 08:35:59

[code]SELECT a.DscrPtr,
SUM(CASE WHEN a.TID = z.TID THEN 1 ELSE 0 END) AS TIDCount,
SUM(CASE WHEN a.TID = z.TID2 THEN 1 ELSE 0 END) AS TID2Count,
SUM(CASE WHEN a.TID = z.TID3 THEN 1 ELSE 0 END) AS TID3Count,
SUM(CASE WHEN a.TID = z.TID THEN 1 ELSE 0 END + CASE WHEN a.TID = z.TID2 THEN 1 ELSE 0 END + CASE WHEN a.TID = z.TID3 THEN 1 ELSE 0 END) AS SumCount
FROM @HIFRAP AS a
LEFT JOIN @MT AS z ON z.Dt BETWEEN @D1 AND @D2
GROUP BY a.DscrPtr
ORDER BY a.DscrPtr[/code]

E 12°55'05.25"
N 56°04'39.16"

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-09-08 : 10:42:50

Thanks !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources