Help with select statement - 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

Help with select statement

Author

Topic

turtle1353
Starting Member

9 Posts

Posted - 2005-12-13 : 17:45:25

Hi SQL gurus!

Some help would be greatly appreciated with this select statement. How how to get column_Category so cost_xxx will output as shown?


Table 1
column_Item   column_Category
1	      Toys
2	      Shoes
3	      Books

Table 2
column_Item	column_Cost    column_Ordered	column_Customer
1	        1.00	       2	        John Doe
2	        1.00	       1	        John Doe
3	        1.50	       2	        John Doe


Needed this Output:
Customer   Cost_Toys	Cost_Shoes	Cost_Books
John Doe   2.00		1.00		3.00

SELECT customer, SUM(cost * ordered) AS Cost_Toys, SUM(cost * ordered) AS Cost_Shoes, SUM(cost * ordered) AS Cost_Books
FROM table1, table2
WHERE customer = 'John Doe' GROUP BY customer

Thanks a lot!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-13 : 17:50:54

Hi turtle!
Welcome to SqlTeam.

SELECT customer, SUM(cost * ordered) AS Cost_Toys, SUM(cost * ordered) AS Cost_Shoes, SUM(cost * ordered) AS Cost_Books
FROM table1 INNER JOIN table2 ON table1.column_Item = table2.column_Item
WHERE customer = 'John Doe' GROUP BY customer

rockmoose

turtle1353
Starting Member

9 Posts

Posted - 2005-12-13 : 18:44:23

Hi rockmoose,

Thanks for that answering so quick! I almost got it but when I run it, the total for cost_xxx come out the same (7.00). I'm trying to get it so that they come out sorted based on column_category. That's where I get stuck. :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-13 : 20:28:02

use order by

SELECT customer, SUM(cost * ordered) AS Cost_Toys, SUM(cost * ordered) AS Cost_Shoes, SUM(cost * ordered) AS Cost_Books
FROM table1 INNER JOIN table2 ON table1.column_Item = table2.column_Item
WHERE customer = 'John Doe' GROUP BY customer
order by table1.column_category

-----------------
[KH]
Learn something new everyday

turtle1353
Starting Member

9 Posts

Posted - 2005-12-13 : 22:37:14

Hi khtan

I tried to use ORDER BY but when I use I get the error:

"Column name 'Table1.category' is invalid in the ORDER BY clause because it is not contained in either an aggreagte function or the GROUP BY clause."

When I try to add table1.category like this:


SELECT Table2.customer, Table1.category, SUM(Table2.cost * Table2.ordered) AS Cost_Toys, SUM(Table2.cost * Table2.ordered) AS cost_shoes, SUM(Table2.cost * Table2.ordered) AS cost_books
FROM Table1 INNER JOIN Table2 ON Table1.item = Table2.item
WHERE (Table2.customer = 'John Doe')
GROUP BY Table2.customer, Table1.category
ORDER BY Table1.category

I'll get this output:
customer     category      cost_toys     cost_shoes     cost_books
John Doe     Books         4             4              4
John Doe     Shoes         1             1              1
John Doe     Toys          2             2              2

What I'm trying to get is this:
customer     cost_toys     cost_shoes     cost_books
John Doe     2             1              4

Getting close!
Thanks khtan!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-13 : 23:45:42

Sorry.. did not read your requirement carefully.

select customer, sum(Toys) as Cost_Toys, sum(Shoes) as Cost_Shoes, sum(Books) as Cost_Books
from
(
	select	t2.customer, 
		case when t1.category = 'Toys' then sum(cost * ordered) else 0 end as [Toys],
		case when t1.category = 'Shoes' then sum(cost * ordered) else 0 end as [Shoes],
		case when t1.category = 'Books' then sum(cost * ordered) else 0 end as [Books]
	from		Table1 t1
	inner join	Table2 t2
		on	t1.item	= t2.item
	group by t2.customer, t1.category
) as t
group by customer

Also refer here for crosstab [url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

-----------------
[KH]
Learn something new everyday

turtle1353
Starting Member

9 Posts

Posted - 2005-12-14 : 00:36:53

Wow! Thanks a lot khtan!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources