How to UNION SQL query

Hello there,

I have these 2 sql query and I want to UNION them to gether and get as one result.Can somebody please help me

cheers
Zolf

SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    SUM(a.AvailQty) as AvailableQty
    FROM 
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id) 
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING 
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode

Open in new window


SELECT
     RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code,
     dbo.Branch.name        AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity
    ) AS AvailQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code

Open in new window

zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
HI,

To apply union to two recordset
you have to have same data type to each column in both query

select col1 from tabl1
union
select col2 from tabl2

Number of columns and datatype should be same
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: UNION ALL instead of UNION unless you want to get a implicit DISTINCT as result of the query (including the resources needed by the dbms to perform that one)
zolfAuthor Commented:
Thanks for your feedbacks. Can somebody help me union those two query,for some reason I am not able to do it.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
how do you try to do it, and what error do you get?
zolfAuthor Commented:
Guy Hengel:

Error I get is

09:45:14  [SELECT - 0 row(s), 0.005 secs]  1) [Error Code: 8155, SQL State: S1000]  No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000]  The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22]  Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.005/0.000 sec  [0 successful, 0 warnings, 1 errors]

SELECT
     code,
     productname,
     Branch.code,
     Branch.name        AS Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code
UNION ALL
SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    null,
    SUM(a.AvailQty) as AvailableQty
    FROM 
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    null,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id) 
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING 
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       ) sq
GROUP BY productcode
       , productname
       , branch

Open in new window

Vikas GargAssociate Principal EngineerCommented:
Hello,

You have to give the null a column name and datatype int

convert(int,null) as SalesQty

in the second record set

Also

 the Branch.code should be given alias as branchcode
zolfAuthor Commented:
Vikas Garg:

Can you please copy the full query with your changes so i know exactly as this is a big query there are lots of chances to make error
Vikas GargAssociate Principal EngineerCommented:
SELECT
     code,
     productname,
     Branch.code as BranchCode,
     Branch.name  AS Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code
UNION ALL
SELECT 
    a.code,
    a.productName,
    a.BranchCode,
    a.Branch,
    CONVERT(INT,null) AS SalesQty,
    SUM(a.AvailQty) as AvailableQty
    FROM 
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    null,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id) 
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING 
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       ) sq
GROUP BY productcode
       , productname
       , branch

Open in new window

zolfAuthor Commented:
No luck, I still get error

1:06:16  [SELECT - 0 row(s), 0.000 secs]  1) [Error Code: 8155, SQL State: S1000]  No column name was specified for column 5 of 'a'. 2) [Error Code: 8156, SQL State: S1000]  The column 'code' was specified multiple times for 'sq'. 3) [Error Code: 207, SQL State: 42S22]  Invalid column name 'productcode'. 4) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.code" could not be bound. 5) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.name" could not be bound.
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]
Vikas GargAssociate Principal EngineerCommented:
Can you try this


SELECT
     code,
     productname,
     Branchcode,
     Branch.name   AS Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code AS Branchcode,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code

UNION ALL
SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailQty,
    SUM(a.AvailQty) as AvailableQty
    FROM 
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    null,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id) 
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING 
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       ) sq
GROUP BY code
       , productname
       , branch

                                          

Open in new window

zolfAuthor Commented:
Now I get another error

No column name was specified for column 5 of 'a'. 2) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.name" could not be bound.
Vikas GargAssociate Principal EngineerCommented:
SELECT
     code,
     productname,
     Branchcode,
     Branch.name   AS Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code AS Branchcode,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code

UNION ALL
SELECT 
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailQty,
    SUM(a.AvailQty) as AvailableQty
    FROM 
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id) 
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING 
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       ) sq
GROUP BY code
       , productname
       , branch

                                          

Open in new window

zolfAuthor Commented:
Thanks for your help!! But now I get another error

Column 'sq.Branchcode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. 2) [Error Code: 4104, SQL State: S1000]  The multi-part identifier "Branch.name" could not be bound.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this:

SELECT
     code,
     productname,
     Branchcode,
    Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (


SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code AS Branchcode,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code


UNION ALL


SELECT
    a.code,
    a.productName,
    a.BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailQty,
    SUM(a.AvailQty) as AvailableQty
    FROM
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode

) sq

GROUP BY code
       , productname
      , Branchcode
       , branch
Vikas GargAssociate Principal EngineerCommented:
SELECT
     code,
     productname,
     Branchcode,
     Branch,
     SUM(AvailQty) AS SalesQty,
   SUM(AvailableQty) as AvailableQty
FROM (
SELECT
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description AS ProductName,
    dbo.Branch.code AS Branchcode,
    dbo.Branch.name AS Branch,
    SUM(dbo.WarehouseProductQuantity.actualQuantity - dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
     cast(null as int) as AvailableQty
FROM
    dbo.WarehouseSubType
INNER JOIN
    dbo.WarehouseType
ON
    (
        dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN
    dbo.WarehouseProductQuantity
ON
    (
        dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.Supplier
ON
    (
        dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.WarehouseSubType1.warehouseSubTypeId IN (1,
                                                 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.id,
    dbo.Tafsil.description ,
     dbo.Branch.code,
     kol.code,
    Mohin.code,
    Tafsil.code

UNION ALL
SELECT
    a.code,
    a.productName,
    a. BranchCode,
    a.Branch,
    CONVERT(INT,null) AvailQty,
    SUM(a.AvailQty) as AvailableQty
    FROM
 (
SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS code,
    dbo.Tafsil.description as productName,
     dbo.Branch.code as BranchCode,
    dbo.Branch.name as Branch,
    SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) as AvailQty
FROM
    dbo.Batch
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Warehouse
ON
    (
        dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Warehouse.branchId = dbo.Branch.id)
        INNER JOIN
    dbo.WarehouseSubType1
ON
    (
        dbo.Warehouse.warehouseSubType1Id = dbo.WarehouseSubType1.id)
        INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
GROUP BY
    dbo.Batch.expiryDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.WarehouseSubType1.warehouseSubTypeId,
    kol.code,
    Mohin.code,
    Tafsil.code,
     dbo.Branch.code
        HAVING
        SUM(dbo.Batch.actualQuantity -  dbo.Batch.reservedQuantity) > 0
        AND  dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
      AND dbo.Batch.expiryDate < dateadd(month,3,getdate())
      )  a
      GROUP BY
      a.code,
       a.productName,
       a.Branch,
       a.BranchCode
       ) sq
GROUP BY code,
     productname,
     Branchcode,
     Branch

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
thanks guys!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.