Combing counts - 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)

Combing counts

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-12-07 : 11:19:52

I have 2 queries -

SELECT dbo_vw_FPO_Automation_2010.JobNo, Count(dbo_vw_FPO_Automation_2010.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2010
WHERE (((dbo_vw_FPO_Automation_2010.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2010.JobNo;

and

SELECT dbo_vw_FPO_Automation_2009.JobNo, Count(dbo_vw_FPO_Automation_2009.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2009
WHERE (((dbo_vw_FPO_Automation_2009.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2009.JobNo;

I want to run 1 query that combines the data as one so I do not get 2 seperate enteries based if an entry is in both queries.

any help would be great. thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 11:23:29

SELECT dbo_vw_FPO_Automation_2010.JobNo, Count(dbo_vw_FPO_Automation_2010.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2010
WHERE (((dbo_vw_FPO_Automation_2010.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2010.JobNo

UNION

SELECT dbo_vw_FPO_Automation_2009.JobNo, Count(dbo_vw_FPO_Automation_2009.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2009
WHERE (((dbo_vw_FPO_Automation_2009.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2009.JobNo

No, you're never too old to Yak'n'Roll if you're too young to die.

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-12-07 : 11:41:46

Thanks.

But 1 thing isn't working ... basically where I got stuck before.

Lets say in both queries I have a Job that is #2 with a count of 3.

I expecting 2, 6 returned

Instead I get 2, 3 returned. it merges it together instead of increasing the count.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 11:53:19

Maybe this?

SELECT JobNo,SUM(CountOfProgram)
FROM
(
SELECT dbo_vw_FPO_Automation_2010.JobNo AS JobNo, Count(dbo_vw_FPO_Automation_2010.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2010
WHERE (((dbo_vw_FPO_Automation_2010.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2010.JobNo

UNION ALL

SELECT dbo_vw_FPO_Automation_2009.JobNo AS JobNo, Count(dbo_vw_FPO_Automation_2009.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2009
WHERE (((dbo_vw_FPO_Automation_2009.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2009.JobNo
) T
GROUP BY JobNo

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 12:04:14

Try this:


select JobNo,sum(CountOfProgram) as CountOfProgram
from
(
SELECT dbo_vw_FPO_Automation_2010.JobNo, Count(dbo_vw_FPO_Automation_2010.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2010
WHERE (((dbo_vw_FPO_Automation_2010.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2010.JobNo

UNION

SELECT dbo_vw_FPO_Automation_2009.JobNo, Count(dbo_vw_FPO_Automation_2009.Program) AS CountOfProgram
FROM dbo_vw_FPO_Automation_2009
WHERE (((dbo_vw_FPO_Automation_2009.ProdDate)>=Date()) AND ((Left([OrderedBy],3))="MDI"))
GROUP BY dbo_vw_FPO_Automation_2009.JobNo
)dt
group by JobNo

No, you're never too old to Yak'n'Roll if you're too young to die.

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-12-07 : 12:32:43

Thanks guys. Both examples worked.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 12:45:11

After reading again I think it should be the solution of vijayisonly.
Because UNION ALL gives duplicates too and this is what you want.
Without that duplicates the sum() has not all needed values for the right result in case of job# 2 with counter 3 twice.

Sorry vijayisonly, I have not read your solution before so my post is unnecessary

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 13:12:18

Np Fred...just glad to help. I just modified your solution in the first place..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources