September 12, 2013 at 8:49 pm
Hello,
I am trying to group by the aggregate results by combining multiple tables, below is what I have, but it is not aggregating as it should. Appreciate the help.
Here is what I have:
Select *
from
(
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C's'
from [dbo].[Table1]
group by [MS]
union
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C's'
from [dbo].[Table2]
group by [MS]) as G1
September 13, 2013 at 12:45 am
R u getting any error?
the code you have posted in not corrrect. It should be like this:
Select *
from
(
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C''s'
from [dbo].[Table1]
group by [MS]
union
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C''s'
from [dbo].[Table2]
group by [MS]) as G1
)ABC
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 12:53 am
Also, be careful while giving alias to a column.
You have put alias to a column as '# of C's' which is also not correct.
It should be like this '# of C''s'.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 12:55 am
VegasL (9/12/2013)
Hello,I am trying to group by the aggregate results by combining multiple tables, below is what I have, but it is not aggregating as it should. Appreciate the help.
Here is what I have:
Select *
from
(
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C's'
from [dbo].[Table1]
group by [MS]
union
select [MS], sum ([Vis]) as 'TV',
sum(Round([VIS]*[GR],1)) as '# of C's'
from [dbo].[Table2]
group by [MS]) as G1
The code you posted is also incorrect. You added one alias and bracket too many at the end.
@VegasL: can you be a bit more specific on "it is not aggregating as it should"?
Table DDL, sample data and desired output would be appreciated.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply