November 7, 2007 at 2:32 pm
Hi All,
I have several queries that return correct data individually, but when I try to combine them, I receive an error message. The individual queries include these...
select ProdGroup, count(ProdCode) as ProdCodePROD
from smcscalc..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
select ProdGroup, count(ProdCode) as ProdCodeANL
from smcscalcanalysis..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
The above return 54 ProdGroup in one column, and corresponding counts. I would like to combine them so that the results appear in three colums: ProdGroup, CalcCountProdCode, CalcCountCACode.
My most recent attempt:
select c.ProdGroup, count(c.ProdCode) as CalcCountProdCode, count(ca.ProdCode) as CalcCountCACode
from smcscalc..ERVProdGrp_ProdCode c, smcscalcanalysis..ERVProdGrp_ProdCode ca
where c.ProcessDate <'12/20/07'
group by c.ProdGroup
This returns only 3 rows, and this message:
(4 row(s) affected)
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Any advice on how I can fix this would be appreciated.
November 7, 2007 at 3:27 pm
There are a few different methods that can be used, which should all recognize that the two queries involved are separate entities
First, try this version. It handles cases where the two tables do not have the same 'ProdCode's. If they are guaranteed to always have the same ProdCodes, remove the IsNull construct, replace the FULL OUTER JOIN with an INNER JOIN, and use either Q1.ProdGroup or q2.ProdGroup.
SELECT IsNull(q1.ProdGroup, q2.ProdGroup),
ProdCodePROD,
ProdCodeANL
FROM (
select ProdGroup,
count(ProdCode) as ProdCodePROD
from smcscalc..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q1
FULL OUTER JOIN (
select ProdGroup,
count(ProdCode) as ProdCodeANL
from smcscalcanalysis..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q2
ON q1.ProdGroup = q2.ProdGroup
Alternately, you can use a version like this one, which counts the items on a line-by-line basis. This has the innate assumption that at least one of the tables contains all of the ProdGroup values required. In this case, i will assume that the smcscalc..ERVProdGrp_ProdCode table contains all of the ProdCodes.
SELECT ProdCode,
Count(*) As ProdCodePROD,
(SELECT COUNT(*) FROM smcscalcanalysis..ERVProdGrp_ProdCode q2
WHERE ProcessDate < '20071220' AND q2.ProdGroup = q1.ProdGroup) As ProdCodeANL
FROM smcscalc..ERVProdGrp_ProdCode q1
WHERE ProcessDate < '20071220'
Group By ProdCode
November 7, 2007 at 3:38 pm
Not sure exatly what you are looking for, but you can try this to start
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM
(
SELECT
ProdGroup,
COUNT(ProdCod) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c,
(
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACode
FROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
WHERE c.ProdGroup = ca.ProdGroup
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 7, 2007 at 3:40 pm
Must remember to post my code in a quoted block, so that formatting is retained.
November 7, 2007 at 3:42 pm
brendt hess (11/7/2007)
Must remember to post my code in a quoted block, so that formatting is retained.
I sure as heck can not figure out how to do that. When I try to copy code into QA when someone used the blocks like I did, it puts everything on one long line.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 7, 2007 at 4:13 pm
brendt hess (11/7/2007)
Must remember to post my code in a quoted block, so that formatting is retained.
Problem is, if you copy from the code block to query analyzer, it really kills the format... you'll need to copy to Word, replace ^l with ^p, recopy, then paste to query analyzer. 😉 I'm creating a wish list for "Tony" so they can fix stuff like that...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2007 at 4:14 pm
I sure as heck can not figure out how to do that. When I try to copy code into QA when someone used the
blocks like I did, it puts everything on one long line.
Greg... see my post above...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 7, 2007 at 6:27 pm
Jeff Moden (11/7/2007)
Problem is, if you copy from the code block to query analyzer, it really kills the format... you'll need to copy to Word, replace ^l with ^p, recopy, then paste to query analyzer. 😉 I'm creating a wish list for "Tony" so they can fix stuff like that...
Wait a minute...I could have sworn I copied and pasted an example of yours right in to QA and it retained its format. Maybe I'm mistaken. Anyhow, what does ^l and ^p mean?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 7, 2007 at 6:33 pm
Greg Snidow (11/7/2007)
Wait a minute...I could have sworn I copied and pasted an example of yours right in to QA and it retained its format. Maybe I'm mistaken. Anyhow, what does ^l and ^p mean?
Ok, I tried what seemed to be the no brainer, which was ctrl+f and do a simple find and replace. It worked. Geez, why do I always think things are more than they are? Anyway, what is ^l and^p, Jeff, and how do you know such things?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 7, 2007 at 6:39 pm
Let's do a quick test. Pasted, no params
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM (
SELECT
ProdGroup,
COUNT(ProdCod) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c
INNER JOIN (
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACodeFROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
ON c.ProdGroup = ca.ProdGroup
Pasted, quoted
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM (
SELECT
ProdGroup,
COUNT(ProdCod) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c
INNER JOIN (
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACodeFROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
ON c.ProdGroup = ca.ProdGroup
CODE block:
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM (
SELECT
ProdGroup,
COUNT(ProdCod) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c
INNER JOIN (
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACode
FROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
ON c.ProdGroup = ca.ProdGroup
November 7, 2007 at 8:24 pm
Greg Snidow (11/7/2007)
Greg Snidow (11/7/2007)
Wait a minute...I could have sworn I copied and pasted an example of yours right in to QA and it retained its format. Maybe I'm mistaken. Anyhow, what does ^l and ^p mean?Ok, I tried what seemed to be the no brainer, which was ctrl+f and do a simple find and replace. It worked. Geez, why do I always think things are more than they are? Anyway, what is ^l and^p, Jeff, and how do you know such things?
Greg
First, the riddle of the easy copy and paste you did... it wasn't in a code block, it was in the normal text of the message... but to get this bloody new forum to accept the leading spaces and embedded multiple spaces to preserve the full formatting I like to do, I had to copy from QA into Word and replace all spaces with &-nbsp (without the dash and is code for "Non-Breaking SPace).
For the ^l and ^p thing... the ^l is the MS Word code for "Manual Line Break" and is equivalent to CHAR(11) or VerticalTab. ^p is the MS Word code for "Paragraph Mark" and is equivalent to CHAR(13) which is a Carriage Return.
How do I know these things? Heh, you know me, Greg... the very first time I had to do a Search'n'Replace in MS Word, I saw the [Special] button, tried everything in it, watched the codes each produced, saved the document and opened it with a hex editor to see what the underlying ASCII codes were.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2007 at 6:44 am
Jeff Moden (11/7/2007)
First, the riddle of the easy copy and paste you did... it wasn't in a code block, it was in the normal text of the message... but to get this bloody new forum to accept the leading spaces and embedded multiple spaces to preserve the full formatting I like to do, I had to copy from QA into Word and replace all spaces with &-nbsp (without the dash and is code for "Non-Breaking SPace).
So are you saying that in Word you replace spaces with '& nbsp' or just '&'? I think I will try it with a view that would other wise look like crap here. Under the special button, non-breaking space is ^t.
ALTER VIEW dbo.vDESIGN_BUILD AS
SELECT TOP 100 PERCENT
CASE WHEN GROUPING(REGION) = 0 AND GROUPING(CO) = 0 AND GROUPING(v.RTE) = 0 THEN region
WHEN GROUPING(REGION) = 0 AND GROUPING(CO) = 0 AND GROUPING(v.RTE) = 1 THEN '*' + UPPER(region) + '*'
WHEN GROUPING(REGION) = 0 AND GROUPING(CO) = 1 AND GROUPING(v.RTE) = 1 THEN NULL
ELSE UPPER(REGION)
END AS REGION,
CASE WHEN GROUPING(REGION) = 0 AND GROUPING(CO) = 0 AND GROUPING(v.RTE) = 1 THEN '*' + UPPER(CO) + ' TOTAL*'
WHEN GROUPING(REGION) = 0 AND GROUPING(CO) = 1 AND GROUPING(v.RTE) = 1 THEN '**' + UPPER(region) + ' TOTAL**'
WHEN GROUPING(REGION) = 1 AND GROUPING(CO) = 1 AND GROUPING(v.RTE) = 1 THEN '***VA TOTAL***'
ELSE co
END AS CO,
v.RTE,
ISNULL(SUM(v.SFU_RTC), 0) AS [SFU RTC],
ISNULL(SUM(v.MDU_RTC), 0) AS [MDU RTC],
ISNULL(SUM(v.SFU_RTC + v.MDU_RTC), 0)AS [TOTAL RTC],
ISNULL(SUM(v.SFU_ODN), 0) AS [SFU ODN],
ISNULL(SUM(v.MDU_ODN), 0) AS [MDU ODN],
ISNULL(SUM(v.SFU_ODN + v.MDU_ODN), 0) AS [TOTAL ODN],
ISNULL(SUM(v.SFU_RTC - v.SFU_ODN), 0) AS [SFU AVAIL],
ISNULL(SUM(v.MDU_RTC - v.MDU_ODN), 0) AS [MDU AVAIL],
ISNULL(SUM(v.SFU_RTC + v.MDU_RTC - v.SFU_ODN - v.MDU_ODN), 0) AS [TOTAL AVAIL]
FROM vBDMS_COUNTS v INNER JOIN
tblbdms_job_info b
ON v.WO_ID = b.WO_ID
WHERE (v.REGION IN ('CENTRAL', 'EASTERN', 'NORVA', 'PIEDMONT'))
AND (v.SFU_ENG + v.MDU_ENG + v.SFU_RTC + v.MDU_RTC + v.SFU_ODN + v.MDU_ODN > 0)
GROUP BY v.REGION, v.CO, v.RTE WITH ROLLUP
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 8, 2007 at 6:46 am
Greg Snidow (11/8/2007)
So are you saying that in Word you replace spaces with '& nbsp' or just '&'? I think I will try it with a view that would other wise look like crap here. Under the special button, non-breaking space is ^t.
Well that did not work. It still came out as one line.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 8, 2007 at 6:52 am
Greg Snidow (11/8/2007)
Greg Snidow (11/8/2007)
So are you saying that in Word you replace spaces with '& nbsp' or just '&'? I think I will try it with a view that would other wise look like crap here. Under the special button, non-breaking space is ^t.Well that did not work. It still came out as one line.
should have been ^s
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
November 8, 2007 at 9:16 am
These are what worked. Thanks!
SELECT IsNull(q1.ProdGroup, q2.ProdGroup) as NameofProdGroup,
ProdCodePROD,
ProdCodeANL
FROM (
select ProdGroup,
count(ProdCode) as ProdCodePROD
from smcscalc..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q1
FULL OUTER JOIN (
select ProdGroup,
count(ProdCode) as ProdCodeANL
from smcscalcanalysis..ERVProdGrp_ProdCode
where ProcessDate <'12/20/07'
group by ProdGroup
) As q2
ON q1.ProdGroup = q2.ProdGroup
and
SELECT
c.ProdGroup,
c.CalcCountProdCode,
ca.CalcCountCACode
FROM
(
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountProdCode
FROM smcscalc..ERVProdGrp_ProdCode
WHERE ProcessDate < '12/20/2007'
GROUP BY ProdGroup
)c,
(
SELECT
ProdGroup,
COUNT(ProdCode) AS CalcCountCACode
FROM smcscalcanalysis..ERVProdGrp_ProdCode
GROUP BY ProdGroup
)ca
WHERE c.ProdGroup = ca.ProdGroup
For whatever reason, the latter seems to be quicker.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply