Geting tow select Statements into One

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

General SQL Server Forums

New to SQL Server Programming

Geting tow select Statements into One

Author

Topic

longinos
Starting Member

11 Posts

Posted - 2008-08-24 : 21:49:02

If I ran this tow select statements I get what I want in tow diffrent results.
How can I do it to have only one result out of the two

select year(date) as 'year', month(date) as 'month', sum(stotal) as 'stotal', sum(tax) as 'tax', sum(total) as 'total'
from corder
group by year(date), month(date)
order by year(date), month(date)
select year(date) as 'year', month(date) as 'month', sum(stotal) as 'stotal', sum(tax) as 'tax', sum(total) as 'total'
from SOrder
group by year(date), month(date)
order by year(date), month(date)

I want my result something like this:

Year month supSubtotal custSubtotal supTax custTax supTotal custTotal

2008--4------100-----------0----------10-----0-------110-------0
2008--5------200----------100---------20-----10------220------110
2008--6-------0-----------50 ----------0----- 5-------0--------55

because some months maybe there is no sale, so we have 0 or even null for some colums, I don't realy care if the year does not apear

Is this posible? can you lead me to the right direction in case it's posible please?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-24 : 21:51:03

[code]
select year(date) as 'year', month(date) as 'month', sum(stotal) as 'stotal', sum(tax) as 'tax', sum(total) as 'total'
from corder
group by year(date), month(date)
~~order by year(date), month(date)~~
UNION ALL
select year(date) as 'year', month(date) as 'month', sum(stotal) as 'stotal', sum(tax) as 'tax', sum(total) as 'total'
from SOrder
group by year(date), month(date)
order by ~~year(date)~~ [year], ~~month(date)~~ [month]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

longinos
Starting Member

11 Posts

Posted - 2008-08-24 : 22:27:42

quote:

Originally posted by khtan


select year(date) as 'cyear', month(date) as 'cmonth', sum(stotal) as 'cstotal', sum(tax) as 'ctax', sum(total) as 'ctotal'
from corder
group by year(date), month(date)
order by year(date), month(date)
UNION ALL
select year(date) as 'syear', month(date) as 'smonth', sum(stotal) as 'sstotal', sum(tax) as 'stax', sum(total) as 'stotal'
from SOrder
group by year(date), month(date)
order by year(date) [year], month(date) [month]

KH
[spoiler]Time is always against us[/spoiler]

That gave me the results in in tow diffrent rows one for the corder and another for the sorder, and I would like both select statements to share the same rows, ending up with more fields per row, having some filds with 0 or null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-24 : 23:09:11

you never explain what do you want clearly in your initial post. How could anybody know what is supSubtotal, custSubtotal ect ?

I am just trying to read you mind and guessing this is what you want.

SELECT	[YEAR], 
	[MONTH],
	supSubtotal 	= SUM(supSubtotal),
	custSubtotal 	= SUM(custSubtotal)
	supTax 		= SUM(supTax),
	custTax 	= SUM(custTax),
	supTotal 	= SUM(supTotal),
	custTotal	= SUM(custTotal)
FROM
(
	SELECT	YEAR(date) 	AS [YEAR], 
		MONTH(date) 	AS [MONTH], 
		SUM(stotal) 	AS [supSubtotal], 
		0 		AS [custSubtotal], 
		SUM(tax) 	AS [supTax],
		0		AS [custTax],
		SUM(total) 	AS [supTotal],
		0		AS [custTotal]
	FROM 	corder
	GROUP BY YEAR(date), MONTH(date)
	
	UNION ALL
	
	SELECT 	YEAR(date) 	AS [YEAR], 
		MONTH(date) 	AS [MONTH], 
		0		AS [supSubtotal],
		SUM(stotal) 	AS [custSubtotal], 
		0		AS [supTax],
		SUM(tax) 	AS [custTax], 
		0		AS [supTotal],
		SUM(total) 	AS [total]
	FROM 	SOrder
	GROUP BY YEAR(date), MONTH(date)
) d
GROUP BY [YEAR], [MONTH]
ORDER BY [YEAR], [MONTH]

KH
[spoiler]Time is always against us[/spoiler]

longinos
Starting Member

11 Posts

Posted - 2008-08-25 : 00:57:30

Sorry that I can not explain my self well, It is because I am beginnig to learn sql and I only do it in my spare time, some times I don't even know what I really want to do
I did a second post on asp forum as my idea was getting better of what I wanted to acomplish and how to explain it better, and that made me post it with a better explanation, and thanks to that somebody just nailed it very well, thanks for your support and fast answers
Here is the solution:

create table #tmporder
(year int,
month int,
sstotal decimal(11,2),
stax decimal(11,2),
stotal decimal(11,2),
cstotal decimal(11,2),
ctax decimal(11,2),
ctotal decimal(11,2))

insert into #tmporder
select year(date), month(date), sum(stotal), sum(tax), sum(total), 0, 0, 0
from SOrder
group by year(date), month(date)
union
select year(date), month(date), 0, 0, 0, sum(stotal), sum(tax), sum(total)
from corder
group by year(date), month(date)

select year, month, sum(sstotal) as SupSubTotal, sum(stax) as SupTax, sum(stotal) as SupTotal,
sum(cstotal) as CustSubTotal, sum(ctax) as CustTax, sum(ctotal) as CustTotal
from #tmporder
group by year, month

drop table #tmporder

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-25 : 01:03:08

You don't really need the temp table. You can use the derived table as in the last query i posted.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources