Calculate sum - 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)

Calculate sum

Author

Topic

Knarf180
Starting Member

42 Posts

Posted - 2004-06-07 : 11:09:06

I have 3 queries and I was wondering if it would be possible to combine it into one and calculate the combined total of all 3.

SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date = Claims_Header.Change_Date) AND
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004')

SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User != '1')

SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND
(Claims_Header.Add_User != '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User = '1')

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-07 : 11:14:28

select cnt1, cnt2, cnt3, tot = cnt1 + cnt2 + cnt3
from
(
select
cnt1 = (
SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date = Claims_Header.Change_Date) AND
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004')
) ,
cnt2 = (
SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User != '1')
) ,
cnt3 = (
SELECT count(*)
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND
(Claims_Header.Add_User != '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User = '1')
)
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TurdSpatulaWarrior
Starting Member

36 Posts

Posted - 2004-06-07 : 11:14:41

Didn't really test if, but probably something like this:


SELECT SUM(Total) FROM
(
SELECT count(*) AS Total
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date = Claims_Header.Change_Date) AND 
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004')

UNION ALL

SELECT count(*) AS Total
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND 
(Claims_Header.Add_User = '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User != '1')

UNION ALL

SELECT count(*) AS Total
FROM
Claims_Header
WHERE
(Claims_Header.Add_Date != Claims_Header.Change_Date) AND 
(Claims_Header.Add_User != '1') and (YEAR(Claims_Header.Add_Date) = '2004') AND
(Claims_Header.Change_User = '1')
) DERIVEDTBL

hth

Knarf180
Starting Member

42 Posts

Posted - 2004-06-07 : 11:22:10

Both work like a charm. Thanks a bunch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources