Use WHERE conditions as column names in query results

user_4934150 0
Viewed 58

Table SQL Fiddle

CREATE TABLE Campaigns (
    Campaign_ID VARCHAR(255),
    Input_Date DATE,
    Start_Date DATE,
    Delivery_Date DATE,
    Offered_Quantity VARCHAR(255),
    Sold_Quantity VARCHAR(255)
);

INSERT INTO Campaigns
(Campaign_ID, Input_Date, Start_Date, Delivery_Date , Offered_Quantity, Sold_Quantity)
VALUES 
("C001","2018-02-16", "2018-03-23","2018-01-25", "500", "300"),
("C002","2018-02-16", "2018-05-12","2018-05-25", "600", "700"),
("C003","2018-02-16", "2018-06-18","2018-06-30", "900", "400"),
("C004","2019-06-20", "2019-07-15","2019-08-02", "930", "800"),
("C005","2019-06-20", "2019-08-02","2017-04-14", "150", "120"),
("C006","2019-06-20", "2019-09-23","2020-10-08", "430", "400"),
("C007","2019-06-20", "2019-11-27","2019-11-26", "850", "900"),
("C008","2019-12-03", "2020-02-08","2020-05-23", "730", "650"),
("C009","2019-12-03", "2020-03-16","2020-02-14", "580", "930");

The data table above contains campaigns in which
a) the Delivery_Date is before the Start_Date or
b) the Quantity_Sold is higher than the Quantity_Offered.

In order to identify those campaigns I use the following query:

SELECT
Input_Date,
Start_Date,
Delivery_Date,
Offered_Quantity,
Sold_Quantity
FROM Campaigns
WHERE Delivery_Date < Start_Date
OR Sold_Quantity > Offered_Quantity
GROUP BY 1;

Now, I want to use the WHERE-Conditions in the query as column names for the results and COUNT the Campaign_ID which fullfill one of the both WHERE-Conditions.

The result should look like this:

Input Date   Delivery_Date < Start_Date    Quantity_Sold > Quantity_Offered
2018-02-16            1                                 1
2019-06-20            2                                 1
2019-12-03            1                                 1

What do I need to change in my query to get this result?

aggregate-functions mysql sql
5 Answers
SELECT Input_Date,
       SUM(Delivery_Date < Start_Date) `Delivery_Date < Start_Date`,
       SUM(Quantity_Sold > Quantity_Offered) `Quantity_Sold > Quantity_Offered`
FROM Campaigns
WHERE Delivery_Date < Start_Date
   OR Quantity_Sold > Quantity_Offered
GROUP BY Input_Date;
user_101387340 
SELECT
  Input_Date,
  SUM(Delivery_Date < Start_Date) AS ` Delivery_Date < Start_Date `,
  SUM(Quantity_Sold > Quantity_Offered) AS ` Quantity_Sold > Quantity_Offered `
FROM Campaigns
WHERE
   Delivery_Date < Start_Date
   OR Quantity_Sold > Quantity_Offered
GROUP BY 1;

Working Demo

Explanation

Boolean expression in MySQL resolves into 0 or 1. So a condition like Delivery_Date < Start_Date will either be resolved into 0 or 1. Thus SUM(Delivery_Date < Start_Date) will be resolved into SUM(1) or SUM(0).

user_17141870

Try this:

SELECT Input_Date,
SUM(CASE WHEN Delivery_Date < Start_Date THEN 1 ELSE 0 END) AS DelDateBefore,
SUM(CASE WHEN Sold_Quantity > Offered_Quantity THEN 1 ELSE 0 END) as SoldHigher
FROM Campaigns
WHERE Delivery_Date < Start_Date
OR Sold_Quantity > Offered_Quantity
GROUP BY Input_Date;

See SQL Fiddle

user_25777340

You can use conditional COUNT as well, with no alias, auto-generated header is quite self-explaining.

SELECT
Input_Date,
count(case when Delivery_Date < Start_Date then 1 end) ,
count(case when Quantity_Sold > Quantity_Offered then 1 end) 
FROM Campaigns
WHERE 
Delivery_Date < Start_Date
OR Quantity_Sold > Quantity_Offered
GROUP BY Input_Date;

Input_Date  count(case when Delivery_Date < Start_Date then 1 end)  count(case when Quantity_Sold > Quantity_Offered then 1 end)
2018-02-16                1                                                1
2019-06-20                2                                                1
2019-12-03                1                                                1
user_62199790

Try CASE Function

SELECT Input_Date, 
   count(CASE WHEN Delivery_Date < Start_Date THEN Campaign_ID END) `Delivery_Date < Start_Date`,
   count(CASE WHEN Sold_Quantity > Offered_Quantity THEN Campaign_ID END) `Sold_Quantity > Offered_Quantity`
FROM Campaigns
GROUP BY Input_Date
user_126455110
Related