I have this table:

I want to see the list of CustomerID grouped by total income per year in separate columns.

I tried this code:

SELECT 
    CustomerID,
    CASE 
        WHEN YEAR(DateofPurchase) = '2015' 
            THEN SUM(income) 
            ELSE 0 
    END AS '2015',
    CASE 
        WHEN YEAR(DateofPurchase) = '2016' 
            THEN SUM(income) 
            ELSE 0 
    END AS '2016',
    CASE 
        WHEN ProductID = 'A' THEN 'Yes' ELSE 'No' END AS 'If bought A'
FROM
    exam
GROUP BY
    CUSTOMERID, productID, YEAR(DateofPurchase)

Expected results:

CustomerID (UNIQUE), Total income 2015, Total income 2016, If bought prod. A(yes/no), if total income (2015 2016) is above 1K (yes/no)

Thank you

CodePudding user response：

see: DBFIDDLE

-- 
SELECT 
   CustomerID,
   SUM(Case when Year(DateofPurchase)='2015' then income else 0 end) as '2015',
   SUM(Case when Year(DateofPurchase)='2016' then income else 0 end) as '2016',
   max(Case when ProductID='A' then 'Yes' else 'No' end) as 'If bought A'
from exam
group by  CUSTOMERID 

• You show group only on CUSTOMER_ID, because you want to see your results per CustomerId, ( and not per CustomerId, productID, year )
• The SUM() should be put outside the CASE WHEN...END, because when the value of this case when is 0 you also need (or can) add it to the sum. This eliminates the need for putting Year in the GROUP BY.
• The MAX(), on 'If bought A', is just a simple trick which works because 'Yes' is larger than 'No'

Finally, the second part of your question: (" if total income (2015 2016) is above 1K (yes/no)" I will leave this as an exercise to you, with the comment that studying the OVER-clause (see: SUM) might help solving it.