 |
|
| Computers Forum Index » Computer - Databases - MS SQL Server » Grouping unions in sqlserver 2005... |
|
Page 1 of 1 |
|
| Author |
Message |
| Jed Fletcher... |
 Posted: Mon Oct 26, 2009 10:38 am |
|
|
|
Guest
|
Hello i have a statement
SELECT DL.Code,P.Description,SUM(Qty) as Qty,SUM(Amount) as Amount,SUM
(DL.CostPrice) as CostPrice
FROM DLitem DL
INNER JOIN DTrans DT
ON (DL.DocNo = DT.DocNo)
INNER JOIN Products P
ON (P.Code = DL.Code)
GROUP BY DL.Code,P.Description
UNION
SELECT DL.Code,P.Description,SUM(-Qty),SUM(-Amount),SUM(-DL.CostPrice)
FROM DLitem DL
INNER JOIN DRec DR
ON (DL.DocNo = DR.RecNo)
INNER JOIN Products P
ON (P.Code = DL.Code)
GROUP BY DL.Code,P.Description
Is there a way i can group the whole statement by using group by? I am
still ending up with rows with the same DL.Code. |
|
|
| Back to top |
|
|
|
| Plamen Ratchev... |
| Posted: Mon Oct 26, 2009 5:54 pm |
|
|
|
Guest
|
You can use a derived table:
SELECT code, description, SUM(qty), SUM(amount), SUM(costprice)
FROM (
<query1>
UNION
<query2>) AS T
GROUP BY code, description;
--
Plamen Ratchev
http://www.SQLStudio.com |
|
|
| Back to top |
|
|
|
| Erland Sommarskog... |
| Posted: Mon Oct 26, 2009 10:30 pm |
|
|
|
Guest
|
|
| Back to top |
|
|
|
| Jed Fletcher... |
| Posted: Tue Oct 27, 2009 6:16 am |
|
|
|
Guest
|
On Oct 26, 10:30 pm, Erland Sommarskog <esq... at (no spam) sommarskog.se> wrote:
Quote: Plamen Ratchev (Pla... at (no spam) SQLStudio.com) writes:
You can use a derived table:
SELECT code, description, SUM(qty), SUM(amount), SUM(costprice)
FROM (
query1
UNION
query2>) AS T
GROUP BY code, description;
And just to clarify Plamen's suggestion: when you do this, you don't
any GROUP BY in the UNION query itself; you only need it in the outer
query.
--
Erland Sommarskog, SQL Server MVP, esq... at (no spam) sommarskog.se
Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks that is the answer |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sat Nov 28, 2009 2:17 pm
|
|