Query Syntax
Query Syntax
SSRS
SSRS
Microsoft SQL Server 2008
Microsoft SQL Server 2008

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: 2015-08-17

Who is Participating?

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

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.

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-17

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

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2015-08-17

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: 2015-08-17

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

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2015-08-17

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

zolfAuthor Commented: 2015-08-17

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

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-17

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: 2015-08-17

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

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-17

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: 2015-08-17

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]

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-17

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: 2015-08-17

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.

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-18

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: 2015-08-18

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.

LVL 57

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented: 2015-08-18

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

LVL 15

Vikas GargAssociate Principal EngineerCommented: 2015-08-18

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: 2015-08-18

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.

Query Syntax

By:

Peter Thorsteinson

C++ 11: Fundamentals

Premium

Premium members can enroll in this course at no extra cost.
Learn More

29 lessons
Query Syntax

By:

Peter Thorsteinson

C++ 11: Intermediate

Premium

Premium members can enroll in this course at no extra cost.
Learn More

53 lessons
Query Syntax

By:

Kumar D

Microsoft SQL Server and Database Concepts

Premium

Premium members can enroll in this course at no extra cost.
Learn More

23 lessons
Query Syntax

By:

Don Kiely

Programming C# 6: Fundamentals

Premium

Premium members can enroll in this course at no extra cost.
Learn More

75 lessons
Query Syntax

By:

Peter Thorsteinson

Python 3 Part 01: Fundamentals

Premium

Premium members can enroll in this course at no extra cost.
Learn More

131 lessons
Query Syntax

By:

Jordan Hudgens

Python 3 Part 02: Tools Modules and JSON

Premium

Premium members can enroll in this course at no extra cost.
Learn More

39 lessons
Query Syntax

By:

Don Kiely

SQL Server Core 2016

Premium

Premium members can enroll in this course at no extra cost.
Learn More

81 lessons
Microsoft Applications

By:

Sandra Batakis

Microsoft Windows 7 Advanced

Premium

Premium members can enroll in this course at no extra cost.
Learn More

183 lessons
Microsoft Applications

By:

Sandra Batakis

Certification
Certification: MOS Microsoft Office Specialist - Access 2007 Ba…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

228 lessons
Microsoft Applications

By:

Tracy Berry

Certification
Certification: MOS Microsoft Office Specialist - PowerPoint 2010…

Premium

Premium members can enroll in this course at no extra cost.
Learn More

203 lessons