SQL - Add column if match else, on aggregate data

Question

I have an order table that roughly looks like this

|Customer id |Date          | revGBP |orderID  |GiftOrder    |
---------------------------------------------------------------------
|1           |1-9-2014      |350     |1        | gift        |
---------------------------------------------------------------------
|2           |1-6-2014      |250     |2        | notGift     |
---------------------------------------------------------------------
|3           |1-9-2014      |200     |3        | gift        |
---------------------------------------------------------------------
|3           |4-9-2014      |200     |4        | notgift     |
---------------------------------------------------------------------
|1           |9-9-2014      |200     |5        | notgift     |
---------------------------------------------------------------------
|2           |1-9-2014      |200     |6        | notgift     |

I want to query using aggregate functions with this

Select
customer_id,
MAX(Date) AS LastOrderDate,
MIN(Date) AS FirstOrderDate,
COUNT(orderID) AS CountOfOrders,
ROUND(SUM(revGBP),2) AS TotalCustSpend,
FROM
Workings.om
Group By customer_email

This works, however I would like to additionally add in whether the customer has ever done a gift transaction, so if customer 1 had an order that contained the word Gift in GiftOrder, add that as a column, otherwise state notGift.

Is this possible?

score 1 · Accepted Answer · edited Dec 21 '16 at 10:13

You can add the number of gifts with either:

SELECT ..., 
    COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) AS number_of_gifts

or the similar (works in MySQL only):

SELECT ..., 
    SUM(GiftOrder='gift') AS number_of_gifts

If you want a simple (yes/no) as in your question, if the customer has ever done one or more gifts vs. none at all, then:

SELECT ..., 
    CASE WHEN COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) > 0 
        THEN 'Gift'
        ELSE 'Not Gift' 
    END AS gifts

SQL - Add column if match else, on aggregate data

1 Answers1