SQL Two Different WHERE Conditions for Two Columns

How can I return two columns that e开发者_Go百科ach 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

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.

You can also use

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

You can probably use the MySQL IF statement on the COUNT

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.

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 

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.

SELECT DISTINCT m.field1, b.field2,

            SUM (CASE
                    WHEN m.created_on >=
                                      TO_DATE ('15/11/2012', 'DD/MM/YYYY')
                    AND m.created_on <
                                   TO_DATE ('15/11/2012', 'DD/MM/YYYY')
                                   + 1
                       THEN 1
                    ELSE 0
                 END
                ) AS count1,
            COUNT (1) AS count2
       FROM table1 m, table2 b
      WHERE m.field1 IN (SELECT DISTINCT field1
                                     FROM table1)
        AND b.field1 = m.field1
   GROUP BY m.field1, b.field2
   ORDER BY count2 DESC;