How to join SQL statements with different where clause on same column of same table

sql, sqlite

Solution

You can do:

SELECT 
    SUM(a.GQTY) AS GrossQty, 
    SUM(a.NQTY) AS NQTY
FROM
(
    SELECT SUM(GrossQty) AS GQTY, SUM(NetQty) AS NQTY 
    FROM   NSales 
    WHERE  IMonth = 5 AND IYear = 2012 AND IDay > 15

    UNION ALL

    SELECT SUM(GrossQty), SUM(NetQty)
    FROM   NSales
    WHERE  IMonth = 6 AND IYear = 2012 AND IDay < 10
) a

Alternatively:

SELECT 
    a.GQTY + b.GQTY AS GQTY,
    a.NQTY + b.NQTY AS NQTY
FROM
(
    SELECT SUM(GrossQty) AS GQTY, SUM(NetQty) AS NQTY 
    FROM   NSales 
    WHERE  IMonth = 5 AND IYear = 2012 AND IDay > 15
) a
CROSS JOIN
(
    SELECT SUM(GrossQty) AS GQTY, SUM(NetQty) NQTY
    FROM   NSales
    WHERE  IMonth = 6 AND IYear = 2012 AND IDay < 10
) b

Or you can use `CASE` expressions and avoid having to issue two separate subselects (harder to read):

SELECT 
    SUM(
        CASE WHEN (IMonth = 5 AND IYear = 2012 AND IDay > 15) OR
                  (IMonth = 6 AND IYear = 2012 AND IDay < 10) THEN
                  GrossQty
             ELSE 0
        END) AS GQTY, 
    SUM(
        CASE WHEN (IMonth = 5 AND IYear = 2012 AND IDay > 15) OR
                  (IMonth = 6 AND IYear = 2012 AND IDay < 10) THEN
                  NetQty
             ELSE 0
        END) AS NQTY
FROM NSales

Problem

I have 2 statements in sqlite 1st Statement: ``` Select SUM(GrossQty) As GQTY, SUM(NetQty) As NQTY From NSales Where IMonth=5 And IYear=2012 And IDay>15 ``` Result: ``` GQty NQty 30 25 ``` 2nd Statement ``` Select SUM(GrossQty) As GQTY, SUM(NetQty) As NQTY From NSales Where IMonth=6 And IYear=2012 And IDay<10 ``` 2nd Result: ``` GQty NQty 20 15 ``` How can I combine these statements so that I get both these results added together like this? Desired Result: ``` GQty NQty 50 40 ```

Original source