Group by & Union In Sql

Question

I am very much confused about this GROUP BY & Union statements.

Right Now I am getting this O/P.

enter image description here

But I want my output to look like this:

   Vendor Name          Purchase Order         Req Qty        Inward Qty
   ----------------------------------------------------------------------       
   Philips Lighting      PO20140918133011        350             350
   (I) Pvt Ltd

Requirement :

If my vendor is null then also I have to group by on the basis of purchase order. But the output which is I am getting in which all the requirement is not satisfied Is there any way that I can do group by only on the basis of purchase order.

**Query : **

SELECT
    vendor_name,
    purchase_order_no,  
    SUM(ISNULL(InwardQty,0)) AS InwardQty,
    SUM(ISNULL(ReqQty,0)) ReqQty
FROM
(
    SELECT 
        vendor_name,
        pm.purchase_order_no,
        ISNULL(SUM(PIIM.qty),0) AS ReqQty,
        0 InwardQty
    FROM  
        RS_Purchase_Order_Master AS PM
        LEFT OUTER JOIN RS_Purchase_Invoice_Info_Master AS PIIM ON PIIM.purchase_order_no = PM.purchase_order_no
        LEFT OUTER JOIN RS_Vendor_Master AS VM ON VM.vendor_id = PM.vendor_id
    WHERE 
        IsPicreated = 1
    GROUP BY 
        vendor_name,
        pm.purchase_order_no

    UNION

    SELECT
        NULL AS vendor_name,
        purchase_order_no,
        0 AS ReqQty,
        ISNULL(SUM(qty),0) AS InwardQty
    FROM
        RS_GIN_Master       
    GROUP BY
        purchase_order_no
)
AS A
GROUP BY
    vendor_name,
    purchase_order_no

Any help or suggestion will appreciated.

Thank you in advance

You should reverse your `ISNULL(SUM(`. If one value is null, but others are not, sum will return null and your whole value will be 0. `SUM(ISNULL(InwardQTY, 0))` — crthompson, Sep 25 '14 at 07:09
thanx For that suggestion Mr. paqogomez. But My Main problem is that I am getting two rows because of that null values in the Vendor Column how should I convert two rows to one row. — Hardik Parmar, Sep 25 '14 at 07:11
Thats why its a comment, not an answer, it will be a problem in your results however. Your main problem is because you're grouping by vendor_name. One vendor_name is phillips, the other is null (null is still a value and its different than phillips if that makes sense). You should remove vendor_name, group by only PO and then join back to get the vendor name. — crthompson, Sep 25 '14 at 07:15

score 1 · Answer 1 · answered Sep 25 '14 at 07:29

I don't know if this is the best solution but I would create a temp table (@a):

declare @a table (
purchase_order_no varchar(100),
TotalReqQty int,
TotalInwardQty int )

insert the result of:

SELECT  purchase_order_no, 
   SUM(ReqQty), 
   SUM(InwardQty)
FROM table 
group by purchase_order_no

And after that I would use inner join to get the vendor using something like this:

SELECT T.Vendor_Name,
   A.purchase_order_no, 
   A.TotalReqQty,
   A.TotalInwardQty
FROM @a a
inner join 
( SELECT DISTINCT Vendor_Name, purchase_order_no
  FROM table WHERE Vendor_Name IS NOT NULL ) T
    ON A.purchase_order_no = T.purchase_order_no

score 1 · Accepted Answer · answered Sep 25 '14 at 08:17

You don't need to create temp table. Just remove vendor_name from GROUP BY, and add MAX(vendor_name).

SELECT
    MAX(vendor_name) AS vendor_name,
    purchase_order_no,  
    SUM(ISNULL(InwardQty,0)) AS InwardQty,
    SUM(ISNULL(ReqQty,0)) AS ReqQty
FROM
(
    SELECT 
        vendor_name,
        pm.purchase_order_no,
        ISNULL(SUM(PIIM.qty),0) AS ReqQty,
        0 InwardQty
    FROM  
       RS_Purchase_Order_Master AS PM
       LEFT OUTER JOIN RS_Purchase_Invoice_Info_Master AS PIIM ON PIIM.purchase_order_no = PM.purchase_order_no
       LEFT OUTER JOIN RS_Vendor_Master AS VM ON VM.vendor_id = PM.vendor_id
    WHERE 
        IsPicreated = 1
    GROUP BY 
        vendor_name,
        pm.purchase_order_no

    UNION

    SELECT
        NULL AS vendor_name,
        purchase_order_no,
        0 AS ReqQty,
        ISNULL(SUM(qty),0) AS InwardQty
    FROM
        RS_GIN_Master       
    GROUP BY
        purchase_order_no
)
AS A
GROUP BY
    purchase_order_no

score 1 · Answer 3 · answered Oct 15 '14 at 02:44

if i look at your query :

Union in sub Query --> to get sum(qty) in table RS_GIN_Master of each purchase_order_no

Sub Query --> make 2 rows to be single rows.

try this one :

SELECT 
    vendor_name,
    pm.purchase_order_no,
    ISNULL(SUM(PIIM.qty),0) AS ReqQty,
    sum(xx.InwardQty) as InwardQty
FROM  
    RS_Purchase_Order_Master AS PM
    LEFT OUTER JOIN RS_Purchase_Invoice_Info_Master AS PIIM ON PIIM.purchase_order_no =      PM.purchase_order_no
    LEFT OUTER JOIN RS_Vendor_Master AS VM ON VM.vendor_id = PM.vendor_id
    LEFT JOIN (
                SELECT purchase_order_no, ISNULL(SUM(qty),0) AS InwardQty
                FROM RS_GIN_Master  
                GROUP BY purchase_order_no
            ) xx On xx.purchase_order_no = pm.purchase_order_no
WHERE 
    IsPicreated = 1
GROUP BY 
    vendor_name,
    pm.purchase_order_no

Group by & Union In Sql

3 Answers3