SQL Two Different WHERE Conditions for Two Columns

Question

How can I return two columns that each use different WHERE critia? Obviously, this won't work:

SELECT Name, COUNT(Column1) AS Total, COUNT(Column1) AS YearToDate
FROM Table1
WHERE Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' --Total
WHERE Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30' --YearToDate

This is the output I'm looking for:

Name  | Total | YTD  
-------------------
Item1 | 2     | 3
Item2 | 4     | 8

Answer 1

You can probably use the MySQL IF statement on the COUNT

Answer 2

If you specify a column name for the COUNT function, it doesn't count NULL values.

So, the simple way would be to use CASE statements to convert the values you don't want counted to NULL

SELECT
    Name,
    COUNT(CASE
            WHEN Occurred_Date >= '2010-01-01' AND Occurred_Date < '2011-01-01'
                THEN Occurred_Date
            ELSE NULL
            END) AS [YTD]
    COUNT(CASE
            WHEN Occurred_Date >= '2010-06-01' AND Occurred_Date < '2011-07-01'
                THEN Occurred_Date
            ELSE NULL
            END) AS [MTD]
FROM Table1
GROUP BY Name

I'm not 100% certain the query engine will let you use CASE within COUNT (I'm not even sure what DB platform you're using), but it gives you the idea. If this way doesn't work, you can write the query using a derived table that will give you the same result.

Answer 3

Seems like a good situation for a UNION.

SELECT Name, COUNT(Column1) AS Total, COUNT(Column1) AS YearToDate
FROM Table1
WHERE Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' --Total
UNION 
SELECT Name, COUNT(Column1) AS Total, COUNT(Column1) AS YearToDate
FROM Table1
WHERE Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30' --YearToDate

Basically you are doing the two queries separately and then combining them into one set.

Not sure what type of DB you are using but here are links for SQL Server and MySql.

Answer 4

You can also use

SELECT m.count, ytd.count FROM 
   (SELECT COUNT( id ) count FROM table WHERE date BETWEEN BETWEEN '2010-06-01' AND '2010-06-30') m, 
   (SELECT COUNT( id ) count FROM table WHERE date BETWEEN BETWEEN '2010-01-01' AND '2010-06-30') ytd 

Answer 5

SELECT COALESCE(CurrMonth.Name, YTD.Name) AS Name, CurrMonth.Total AS Total, YTD.Total AS YearToDate
FROM (
    SELECT Name, COUNT(Column1) AS Total
    FROM Table1 
    WHERE Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' --Total 
    GROUP BY Name
) AS CurrMonth
FULL OUTER JOIN
(
    SELECT Name, COUNT(Column1) AS Total
    FROM Table1 
    WHERE Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30' --YearToDate 
    GROUP BY Name
) AS YTD
ON CurrMonth.Name = YTD.Name

The full outer join is not necessary, but just demonstrates how you might need to handle a case where one set is not a strict subset of the other. I would typically use the YTD subquery LEFT JOIN to the current month subquery.

Another strategy - using CASE:

SELECT Name
    ,COUNT(CASE WHEN Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' THEN Column1 ELSE NULL END) AS Total 
    ,COUNT(CASE WHEN Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30' THEN Column1 ELSE NULL END) AS YearToDate
FROM Table1  
WHERE Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' -- (unnecessary)
    OR Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30'
GROUP BY Name 

Answer 6

SELECT Name
    ,SUM(
        CASE WHEN Occurred_Date BETWEEN '2010-06-01' AND '2010-06-30' 
        THEN 1 
        ELSE 0 
        END) AS Total 
    ,SUM(
        CASE WHEN Occurred_Date BETWEEN '2010-01-01' AND '2010-06-30' 
        THEN 1 
        ELSE 0 
        END) AS YearToDate
FROM Table1  
GROUP BY Name 

EDIT: This should work in SQL Server.