Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

SQL Query Help

I have the following query

SELECT     WH as warehouse, COUNT(order_no) AS backorder,null as late
FROM        table1
GROUP BY WH

union all
SELECT     WH as warehouse, null as backorder,COUNT(order_no) AS late
from table2
GROUP BY WH

This returns the following
Warehouse   BackOrders              Late
B                          25                         NULL
E                          12                         NULL
F                          1                         NULL
G                          41                    NULL
S                          8                         NULL
B                        NULL                   11
E                        NULL                   11
F                        NULL                    1
G                        NULL                   33
S                        NULL                    6

Is there a way of getting the results to show in one block so that Warehouse B has a number for backorders and late on the same line? (hopefully you understand what I need..)

Thanks for the help!
0
sagarh
Asked:
sagarh
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Add COALESCE() to the queries to convert nulls to zeros, throw the whole thing in a subquery, then group by Warehouse and Sum() by the rest.


SELECT a.Warehouse, Sum(a.BackOrders) as BackOrders, Sum(a.Late) as Late
FROM (
SELECT     WH as warehouse, COALESCE(COUNT(order_no),0) AS backorder,null as late
FROM        table1
GROUP BY WH
union all
SELECT     WH as warehouse, null as backorder,COALESCE(COUNT(order_no),0) AS late
from table2
GROUP BY WH) a
GROUP BY Warehouse
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now