Select statement question - SQL Server Forums

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

Select statement question

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-01-19 : 08:55:12

I am having trouble with a select(Select Union Select). Here is breifly what I am trying. I am getting an error on the last group by statement. Maybe there is a better way. Thanks for your help, vmon

Select Item, Sum(Open), Sum(Closed), (Sum(Sum(Open) + Sum(Closed)) As Total From (Select Item, Sum(Qty) As Open, 0 As Closed From tblOpenOrder Group By Item Union Select Item, 0 As Open, Sum(Qty) As Closed From tblClosedOrder Group By Item) Group By Item

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-19 : 08:59:11

Try this:

Select 
	Item, Sum(Open), Sum(Closed), Sum([Open] + Closed) As Total 
From 
	(
	Select 
		Item, Sum(Qty) As [Open], 0 As Closed 
	From tblOpenOrder 
	Group By Item
	Union 
	Select
		Item, 0 As [Open], Sum(Qty) As Closed 
	From tblClosedOrder
	Group By Item
	)
Group By Item

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 09:06:00

You would be better of normalizing your data. In your case, adding a column named "Status" with the two possible values "Open" and "Closed" would be a good choice.

However, in your query above, you miss a paranthesis and there are so many things wrong.

Try this instead

-- prepare sample date
declare	@openorders table (item int, qty int)

insert	@openorders
select	1, 100 union all
select	2, 50

declare	@closedorders table (item int, qty int)

insert	@closedorders
select	1, 100 union all
select	3, 50

-- show the data
select		isnull(o.item, c.item) as item,
		sum(isnull(o.qty, 0)) as openqty,
		sum(isnull(c.qty, 0)) as closedqty,
		sum(isnull(o.qty, 0) + isnull(c.qty, 0)) as totalqty
from		@openorders as o
full join	@closedorders as c on c.item = o.item
group by	isnull(o.item, c.item)
order by	isnull(o.item, c.item)

Peter Larsson
Helsingborg, Sweden

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-01-19 : 09:06:47

It is not the Open column. The error is incorrect syntax near group. I have narrowed the error to the last group since the select union select works.

Thanks, vmon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 09:14:12

This is your original query dissected.

Select		Item,
		Sum(Open),
		Sum(Closed),
		(
			SELECT   -- Where is the SELECT statement for the subquery?
			Sum( Sum(Open) + Sum(Closed)  -- Why sum a sum? this should either be a select or no double sum at all
			) As Total
			From		(
						Select		Item,
								Sum(Qty) As Open,
								0 As Closed
						From		tblOpenOrder
						Group By Item
						Union
						Select		Item,
								0 As Open,
								Sum(Qty) As Closed
						From		tblClosedOrder
						Group By Item
					)  as x --where is the derived table alias?
					-- where is the check for only getting the total for current item in outer select?
			Group By	Item
		) as d -- Entire row missing!
GROUP BY Item   -- Entire row missing!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 09:16:23

Both Harsh and I provided a fully functional replacement suggestions for you.
Use them if you can't understand nor fix your own query.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-19 : 09:26:12

Look how easy the query would be if the two order tables were merged into one as suggested above

select		item,
		sum(case when status = 'open' then qty else 0 end) as openqty,
		sum(case when status = 'closed' then qty else 0 end) as closedqty,
		sum(qty) as totalqty
from		orders
group by	item
order by 	item

To emulate normalized tables my suggestion above is not that far from

-- show the data
select		isnull(o.item, c.item) as item,
		sum(isnull(o.qty, 0)) as openqty,
		sum(isnull(c.qty, 0)) as closedqty,
		sum(isnull(o.qty, 0) + isnull(c.qty, 0)) as totalqty
from		tblopenorders as o
full join	tblclosedorders as c on c.item = o.item
group by	isnull(o.item, c.item)
order by	isnull(o.item, c.item)

Peter Larsson
Helsingborg, Sweden

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-01-19 : 09:42:46

THANKS SO MUCH. I am much closer to understanding what I have to do. I don't have option to normalize the tables since it is a purchased software package and database is already defined. I am just trying to get some data turned into usable information. I am also a self taught novice with sql. Thanks again. vmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources