CODEWITHSUNDEEP
sql-servergroup-by
Patrick Yeung
Patrick Yeung

Reputation: 3

SQL Server - Group several tables

I have queried several tables and want to merge them together with the 1st same column data. Here is the queries,

1st query

  **|Locations|PO Qty|PO NET COST|**    
             |AAA|100|1000|

2nd query

**|Locations|Transfer Qty|Transfer COST|**

|BBB|200|2000|

3rd query

**|Locations|Adjusted Qty|Adjusted COST|**    
|CCC|300|3000|

4th query

**|Locations|Sold Qty|Cost of Goods Sold|**    
|DDD|400|4000|

5th query

**|Locations|Qty on Hand|Cost on Hand|**    
|EEE|500|5000|

I want to group like as below format...

**|Locations|PO Qty|PO NET COST|Transfer Qty|Transfer Cost|Adjusted Qty|AdjustedCost|Sold Qty|Cost of Goods Sold|Qty on Hand|Cost on Hand|**

(total 11 columns)

|AAA|100|1000|0|0|0|0|0|0|0|0|
---   
|BBB|0|0|200|2000|0|0|0|0|0|0|
---
|CCC|0|0|0|0|300|3000|0|0|0|0|
---
|DDD|0|0|0|0|0|0|400|4000|0|0|
----
|EEE|0|0|0|0|0|0|0|0|500|5000|

Thank you! Patrick

Upvotes: 0

Views: 43

Answers (2)

Patrick Yeung
Patrick Yeung

Reputation: 3

Thank for you prompt reply, it is not quite well if I put 0 in all fields that do not have data, because there will be other location data with other columns etc. Transfer Qty, Qty on Hand... May be I provide my real queries (1st and 2nd) as below for better understanding. I use SUM for all QTY and COST data fields, because I need to sum up the total and there contains filtering criteria. Also, I am not allowed to use UNION ALL between these 2 SELECT queries. It said "Incorrect syntax near the keyword 'UNION'".

`SELECT [Location Code], SUM(Quantity) AS "PO Recpt Qty" From [DATABASE].[dbo].[Company$Item Ledger Entry]
Where [DATABASE].[dbo].[Company$Item Ledger Entry].[Quantity] > 0 AND [Database].[dbo].[Company$Item Ledger Entry].[Document Type] = '5' AND [DATABASE].[dbo].[Company$Item Ledger Entry].[Posting Date] > '2014-08-01' Group by [Location Code] Order by [Location Code]

SELECT [Location Code], SUM(Quantity) AS "PO QTY (from Purchase line table)", SUM ([Unit Cost (LCY)]) AS "PO Net Cost(HK) " From [DATABASE].[dbo].[Company $Purchase Line] Where [DATABASE].[dbo].[Company$Purchase Line].[Quantity] > 0 Group by [Location Code] Order by [Location Code]`

The 1st query results :

|Location Code|PO Recpt Qty|

|KOREA|263|

|MARKETING|1425|

|MOH|1261|

|SAMPLE|1351|

|WHSDAMAGE|34|

|WHSMOH|19455|

|WHSMOI|39276|

The 2nd query results :

|Location Code|PO QTY (from Purchase line table)|PO Net Cost(HK)|

|KOREA|263|16793.64|

|MARKETING|577|0|

|SAMPLE|1351|200543.22|

|WHSDAMAGE|17||2354.76|

|WHSMOH|32202|980046.99|

|WHSMOI||42395|986143.32|

Looking for this combined results with using UNION ALL

|Location Code|PO Recpt Qty|PO QTY (from Purchase line table)|PO Net Cost(HK)|

|KOREA|263|263|16739.64|

|MARKETING|1425|577|0|

|MOH|1261|0|0|

|SAMPLE|1351|1351|200543.22|

|WHSDAMAGE|34|17|2354.76|

|WHSMOH|19455|32203|980046.99|

|WHSMOI|39276|42395|986143.32|

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

A UNION ALL operator will give you the result you're after. The thing to note is that the first query you produce will define the data types and names of the columns, every query after this must have the same columns in the same order.

In the example below you'll notice that in any columns that don't contain data I've put in a zero. You'll want to replace the fields that actually contain data with your fields from the queries. Leave the zero values where they are otherwise the UNION won't work.

SELECT 'AAA' AS Locations, 100 AS POQty, 1000 AS PONetCost, 0 AS TransferQuantity, 0 AS TransferCost, 0 AS AdjustedQty, 0 AS AdjustedCost,0 AS SoldQty,0 AS CostofGoodsSold,0 AS QtyOnHand, 0 AS CostOnHand FROM Table1
UNION ALL
SELECT 'BBB', 0, 0, 200, 2000, 0, 0, 0, 0, 0, 0 FROM Table2
UNION ALL
SELECT 'CCC', 0, 0, 0, 0, 300, 3000, 0, 0, 0, 0 FROM Table3

The query below is the same but I've left in the field names in the sub query so that it's a little easier to read. Notice that the fields are all the same and in the same order, this is essential.

SELECT 
    'AAA' AS Locations, 
    100 AS POQty, 
    1000 AS PONetCost, 
    0 AS TransferQuantity, 
    0 AS TransferCost, 
    0 AS AdjustedQty, 
    0 AS AdjustedCost,
    0 AS SoldQty,
    0 AS CostofGoodsSold,
    0 AS QtyOnHand, 
    0 AS CostOnHand 
FROM Table1
UNION ALL
SELECT 
    'BBB' AS Locations, 
    0 AS POQty, 
    0 AS PONetCost, 
    200 AS TransferQuantity, 
    2000 AS TransferCost, 
    0 AS AdjustedQty, 
    0 AS AdjustedCost,
    0 AS SoldQty,
    0 AS CostofGoodsSold,
    0 AS QtyOnHand, 
    0 AS CostOnHand 
FROM Table2
UNION ALL
SELECT 
    'CCC' AS Locations, 
    0 AS POQty, 
    0 AS PONetCost, 
    0 AS TransferQuantity, 
    0 AS TransferCost, 
    300 AS AdjustedQty, 
    3000 AS AdjustedCost,
    0 AS SoldQty,
    0 AS CostofGoodsSold,
    0 AS QtyOnHand, 
    0 AS CostOnHand 
FROM Table3

Removing the FROM statements from above and just running the absolute values will give the following result;

Locations   POQty   PONetCost   TransferQuantity    TransferCost    AdjustedQty AdjustedCost    SoldQty CostofGoodsSold QtyOnHand   CostOnHand
AAA         100     1000        0                   0               0           0               0       0               0           0
BBB         0       0           200                 2000            0           0               0       0               0           0
CCC         0       0           0                   0               300         3000            0       0               0           0

Upvotes: 1

Related Questions