January 29, 2009 at 8:22 am
Hello,
I have an issue with this query.
What I want is to sum qty1 in table2 and qty2 in table3 and group this by loc in table2 where table1.id = '45'
Table1
id desc
45 test1
Table2
qty1 id loc
3 45 1
5 45 1
1 65 2
Table3
qty2 id loc
7 45 1
2 45 2
1 12 2
Desired output from above would be:
id desc loc qty1 qty2
45 test1 1 8 7
45 test1 2 null 2
Is this possible?
Thanks
January 29, 2009 at 8:27 am
One more thing, show us what you have done so far to solve this problem.
January 29, 2009 at 8:56 am
Just a follow-up, once you show us what you have done so far, I have put together a solution for you to check out.
January 29, 2009 at 9:11 am
SELECT table1.id,
table1.[desc],
table2.loc
sum(table2.qty1),
sum(table3.qty2)
FROM table2
INNER JOIN table1 on table2.id = table1.id
INNER JOIN table3 on table2.id = table3.id
where table1.id = '45'
GROUP BY table2.loc, table1.id, table1.[desc]
But this doesnt seem to be right
January 29, 2009 at 9:33 am
Okay, here is the code I put together. Just to be sure, I am using CTE's so this will only work in SQL Server 2005/2008.
An explanation is due first. I have created two CTE's to sum the quantities by id (TableID) and loc (Loc). I then join these together using a full outer join on id and loc as we want all information from both tables.
Once that is done, I then join that with and inner join to the first table as we only want the information related to the data in that table.
Make sense?
Code to follow, I am having problems getting it to post.
January 29, 2009 at 9:38 am
Okay, here is the code I put together. Just to be sure, I am using CTE's so this will only work in SQL Server 2005/2008.
I am going to upload it and hope that works.
January 29, 2009 at 9:39 am
Okay, it's up. that is just totally weird that I couldn't post the code in the reply itself.
Anyone else ever experience that problem.
January 29, 2009 at 9:47 am
I'm still on 2000, so I'm not into CTE's (yet)... So this would be my solution:SELECT id, [desc], loc, SUM(qty1), SUM(qty2)
FROM (
SELECT T1.id, T1.[desc], T2.loc, T2.qty1, 0 qty2
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.id = T1.id
WHERE T1.id = 45
UNION
SELECT T1.id, T1.[desc], T3.loc, 0, T3.qty2
FROM Table3 T3
INNER JOIN Table1 T1 ON T3.id = T1.id
WHERE T1.id = 45
) tmp
GROUP BY id, [desc], loc
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 29, 2009 at 9:59 am
In SQL Server 2000, turn each CTE into a derived table in the from clause.
If you need more help than that let me know. I have a meeting to run off to right now.
January 30, 2009 at 4:37 am
Nils Andersson (1/30/2009)
Great stuff.Im on sql 2000 here som im not able to use cte, sorry.
@r.hensbergen, your query works perfect but I dont see the logic in it.
Can you explain for example what this does
0 qty2
What I did is make 2 queries with the same size (column 1 in the first query should have the same meaning as column 1 in the second query, etc), pasted these together with UNION and then grouped that, summarizing the 2 qty columns. So I had to add the 0 qty2 part in the first query and the 0 qty1 part in the second query, so they result in 2 different columns in the output. If you're unknown with UNION, read this: http://msdn.microsoft.com/en-us/library/aa260653(SQL.80).aspx and the linked article about SELECT.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
January 30, 2009 at 7:13 am
Nils Andersson (1/30/2009)
Great stuff.Im on sql 2000 here som im not able to use cte, sorry.
@r.hensbergen, your query works perfect but I dont see the logic in it.
Can you explain for example what this does
0 qty2
@Lynn petis, thanks for your help. I will study your solution and come back.
Nils, I provided you with a SQL Server 2005 solution because you posted your question in a SQL Server 2005 forum. Based on that, and no other information, that's what you got.
Please try to post future SQL Server 2000 questions in the SQL Server 2000 forums. It will help you get answers that are more appropriate to your current platform.
As I said above, however, you can convert the CTE's into derived tables in the FROM clause of the query and achieve the same results.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply