We help IT Professionals succeed at work.

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!
Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.