January 27, 2016 at 7:28 am
I'm not sure I understand why but my IND_Desc does not seem to be grouping between the union. An example would be I have a IND_DESC in both tables called printers and instead of adding them together each one is printing seperately.
Here is my query
use ******;
SELECT
d.IND_DESC [Industry]
,SUM(CAST (c.SALE_PRICE1 as MONEY)) [Order Totals]
from jc10 a
left outer join JC11 c on a.JOB_NUMB = c.JOB_NUMB
left outer join JC17 d on a.IND_CODE = d.CODE
where BILLTO_NUMB <> 99999
and CAST(c.SALE_PRICE1 as MONEY) > 0
and CONVERT(CHAR(10), DATEADD(d, a.RDAT_ENTERED +5843, 0), 1) =
(CASE
WHEN DATENAME(W, GETDATE ()) = 'MONDAY' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 1)
ELSE CONVERT(VARCHAR(10), GETDATE() -1,1)
END)
GROUP BY
d.IND_DESC
UNION ALL
SELECT
d.IND_DESC [Industry]
,SUM(CAST (c.SALE_PRICE1 as MONEY)) [Order Totals]
from oe10 a
left outer join OE11 c on a.ORD_NUMB = c.ORD_NUMB
left outer join JC17 d on a.IND_CODE = d.CODE
where BILLTO_NUMB <> 99999
and CAST(c.SALE_PRICE1 as MONEY) > 0
and CONVERT(CHAR(10), DATEADD(d, a.RDAT_ENTERED +5843, 0), 1) =
(CASE
WHEN DATENAME(W, GETDATE ()) = 'MONDAY' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 1)
ELSE CONVERT(VARCHAR(10), GETDATE() -1,1)
END)
GROUP BY
d.IND_DESC
January 27, 2016 at 7:59 am
mjakachira (1/27/2016)
I'm not sure I understand why but my IND_Desc does not seem to be grouping between the union. An example would be I have a IND_DESC in both tables called printers and instead of adding them together each one is printing seperately.Here is my query
use ******;
SELECT
d.IND_DESC [Industry]
,SUM(CAST (c.SALE_PRICE1 as MONEY)) [Order Totals]
from jc10 a
left outer join JC11 c on a.JOB_NUMB = c.JOB_NUMB
left outer join JC17 d on a.IND_CODE = d.CODE
where BILLTO_NUMB <> 99999
and CAST(c.SALE_PRICE1 as MONEY) > 0
and CONVERT(CHAR(10), DATEADD(d, a.RDAT_ENTERED +5843, 0), 1) =
(CASE
WHEN DATENAME(W, GETDATE ()) = 'MONDAY' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 1)
ELSE CONVERT(VARCHAR(10), GETDATE() -1,1)
END)
GROUP BY
d.IND_DESC
UNION ALL
SELECT
d.IND_DESC [Industry]
,SUM(CAST (c.SALE_PRICE1 as MONEY)) [Order Totals]
from oe10 a
left outer join OE11 c on a.ORD_NUMB = c.ORD_NUMB
left outer join JC17 d on a.IND_CODE = d.CODE
where BILLTO_NUMB <> 99999
and CAST(c.SALE_PRICE1 as MONEY) > 0
and CONVERT(CHAR(10), DATEADD(d, a.RDAT_ENTERED +5843, 0), 1) =
(CASE
WHEN DATENAME(W, GETDATE ()) = 'MONDAY' THEN CONVERT(VARCHAR(10), GETDATE() - 3, 1)
ELSE CONVERT(VARCHAR(10), GETDATE() -1,1)
END)
GROUP BY
d.IND_DESC
Not sure what the problem is here. Are you wanting to get a sum of both groups or something like that. Remember that when using a UNION it executes each query and puts the results together. And since you are using UNION ALL it will NOT exclude duplicate rows. If you are trying to get a sum of values from those two queries you would need to add an outer query to get the sum of the values returned in your query.
something like this:
select Industry
, SUM([Order Totals]) as OrderTotal
from
(
[YourQueryHere]
) x
group by Industry
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2016 at 9:12 am
Tried that and it didn't like the syntax. Going to keep trying I think i get it but any ideas?
Also when I just use UNION it does the same thing.
Figured it out but still curious as to why the UNION did not get rid of the dup row
January 27, 2016 at 9:25 am
mjakachira (1/27/2016)
Tried that and it didn't like the syntax. Going to keep trying I think i get it but any ideas?Also when I just use UNION it does the same thing.
Figured it out but still curious as to why the UNION did not get rid of the dup row
Probably because you have different values in the [Order Totals] column. That would make both rows unique.
UNION, UNION ALL, EXCEPT & INTERSECT are all set operators, which mean that they work with sets. GROUP BY is part of the definition from each set, which is why it won't work for the whole query.
An alternative to the derived table suggested by Sean is to use a CTE. That should work exactly the same and it's just a different way to write the query.
WITH IndustryTotals AS(
SELECT Industry, SUM(c.SALE_PRICE) AS [Order Totals]
FROM SomeTables
GROUP BY Industry
UNION ALL
SELECT Industry, SUM(c.SALE_PRICE) AS [Order Totals]
FROM OtherTables
GROUP BY Industry
)
SELECT Industry
, SUM([Order Totals]) as OrderTotal
FROM IndustryTotals
GROUP BY Industry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply