How to use select value in next or another select value MySQL?-CodePudding

I want to use tahun in first select distinct value in AND tahun like ? like foreach in programming language

my code is like this

select distinct SUBSTRING_INDEX(tahun,'/', -1) as tahun,
    COUNT(CASE WHEN choice = "A" AND tahun like ' 08%' THEN 1 ELSE NULL END) AS A, 
    COUNT(CASE WHEN choice = "B" AND tahun like ' 08%' THEN 1 ELSE NULL END) AS B, 
    COUNT(CASE WHEN choice = "C" AND tahun like ' 08%' THEN 1 ELSE NULL END) AS C
FROM tes;

Sorry if my english is bad, Thank you in advance for your answer

distint tahun values

CodePudding user response：

You have to GROUP the records instead of put DISTINCT

SELECT SUBSTRING_INDEX(tahun,'/', -1) as tahun,
    SUM(CASE WHEN choice = 'A' THEN 1 ELSE 0 END) AS A, 
    SUM(CASE WHEN choice = 'B' THEN 1 ELSE 0 END) AS B, 
    SUM(CASE WHEN choice = 'C' THEN 1 ELSE 0 END) AS C
FROM tes
GROUP BY SUBSTRING_INDEX(tahun,'/', -1)
ORDER BY 1;