How to UNION SQL query

zolf
zolf used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Vikas GargAssociate Principal Engineer
Top Expert 2014

Commented:
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 Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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)
zolf

Author

Commented:
Thanks for your feedbacks. Can somebody help me union those two query,for some reason I am not able to do it.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Learn More!
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
how do you try to do it, and what error do you get?
zolf

Author

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 Engineer
Top Expert 2014

Commented:
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
zolf

Author

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 Engineer
Top Expert 2014

Commented: 
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

zolf

Author

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 Engineer
Top Expert 2014

Commented:
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

zolf

Author

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 Engineer
Top Expert 2014
Commented: 
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

zolf

Author

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 ChongSoftware Team Lead
Commented:
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
Associate Principal Engineer
Top Expert 2014
Commented:
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
zolf

Author

Commented:
thanks guys!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial