SQL Query Help

Posted on 2012-08-16
Last Modified: 2012-08-16
I have the following query

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

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

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!
Question by:sagarh
    1 Comment
    LVL 65

    Accepted Solution

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

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now