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 2005 Forums
 Transact-SQL (2005)
 Problem with multiple sum in different tables

Author  Topic 

trallador
Starting Member

13 Posts
Posted - 2009-07-24 : 03:54:56
I have a problem with a 2 sum colums in different tables:

the two separate querys:



SELECT articulo, definicion, SUM(unidades) AS 'u_comp', AVG(precio) AS 'avg_precio' FROM d_albcom GROUP BY articulo
SELECT articulo, definicion, SUM(unidades) AS 'u_vent', AVG(precio) AS 'avg_precio' FROM d_albven GROUP BY articulo


If i use UNION works, but i need the results in colums not in rows :&
and i tried something like this:



SELECT article, definicio, SUM(u_comp), SUM(u_vent), AVG(avg_preu_comp), AVG(avg_preu_vent)
FROM (
	SELECT articulo AS 'article', definicion AS 'definicio', unidades AS 'u_comp', precio AS 'avg_preu_comp' FROM d_albcom
		UNION ALL
	SELECT articulo AS 'article', definicion AS 'definicio', unidades AS 'u_vent', precio AS 'avg_preu_vent' FROM d_albven
)
GROUP BY article
ORDER BY article


not works :(

Any can help me? Thanks for all.

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-07-24 : 04:15:14
Try this

select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_comp,
	avg(case when sno=2 then precio else 0 end) as avg_comp
from
(
SELECT 1 as sno,articulo, definicion, (unidades) AS 'u_comp', (precio) AS 'avg_precio' FROM d_albcom GROUP BY articulo
union all
SELECT 2 , articulo, definicion, (unidades) AS 'u_vent', (precio) AS 'avg_precio' FROM d_albven GROUP BY articulo
) as t
group by
	 articulo, definicion



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

trallador
Starting Member

13 Posts
Posted - 2009-07-24 : 04:40:55
Thx madhivanan for fast response but not work :(

unfortunately got the same result with this:



select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_precio_comp,
	avg(case when sno=2 then precio else 0 end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, (unidades) AS 'u_comp', (precio) AS 'avg_precio_comp' FROM d_albcom GROUP BY articulo)
union all
(SELECT 2 as sno, articulo, definicion, (unidades) AS 'u_vent', (precio) AS 'avg_precio_vent' FROM d_albven GROUP BY articulo) 
)
group by
	 articulo, definicion
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-07-24 : 05:57:43

Wrong use of column names

select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_precio_comp,
	avg(case when sno=2 then precio else 0 end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom GROUP BY articulo)
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven GROUP BY articulo) 
)
group by
	 articulo, definicion



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

trallador
Starting Member

13 Posts
Posted - 2009-07-24 : 06:11:52
This produce the same error: Incorrect syntax near 'group' (last group by) :&
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2009-07-24 : 06:17:27
quote:
Originally posted by madhivanan


Wrong use of column names

select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_precio_comp,
	avg(case when sno=2 then precio else 0 end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom GROUP BY articulo)
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven GROUP BY articulo) 
)as t
group by
	 articulo, definicion



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

trallador
Starting Member

13 Posts
Posted - 2009-07-24 : 06:52:28
now works :D



select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then (precio) else NULL end) as avg_precio_comp,
	avg(case when sno=2 then (precio) else NULL end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom )
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven ) 
)as t
group by
	 articulo, definicion
order by articulo


I use the NULL because AVG uses the 0 to calculate the average, a more efficient option?

Big thanks to both for the help :)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-07-24 : 06:53:17
quote:
Originally posted by ayamas

quote:
Originally posted by madhivanan


Wrong use of column names

select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_precio_comp,
	avg(case when sno=2 then precio else 0 end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom GROUP BY articulo)
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven GROUP BY articulo) 
)as t
group by
	 articulo, definicion



Madhivanan

Failing to plan is Planning to fail


Thanks. I just copied from OP's query where alias was missing

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-07-24 : 06:54:19
quote:
Originally posted by trallador

now works :D



select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then (precio) else NULL end) as avg_precio_comp,
	avg(case when sno=2 then (precio) else NULL end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom )
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven ) 
)as t
group by
	 articulo, definicion
order by articulo


I use the NULL because AVG uses the 0 to calculate the average, a more efficient option?

Big thanks to both for the help :)

Yes. For count/Avg better to use NULL

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2009-07-24 : 07:23:56
quote:
Originally posted by madhivanan

quote:
Originally posted by ayamas

quote:
Originally posted by madhivanan


Wrong use of column names

select articulo, definicion,
	sum(case when sno=1 then unidades else 0 end) as u_comp,
	sum(case when sno=2 then unidades else 0 end) as u_vent,
	avg(case when sno=1 then precio else 0 end) as avg_precio_comp,
	avg(case when sno=2 then precio else 0 end) as avg_precio_vent
from
(
(SELECT 1 as sno, articulo, definicion, unidades , precio FROM d_albcom GROUP BY articulo)
union all
(SELECT 2 as sno, articulo, definicion, unidades, precio FROM d_albven GROUP BY articulo) 
)as t
group by
	 articulo, definicion



Madhivanan

Failing to plan is Planning to fail


Thanks. I just copied from OP's query where alias was missing

Madhivanan

Failing to plan is Planning to fail

You are welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site