CODEWITHSUNDEEP
phpmysqlgroup-bycasecase-when
maikelsabido
maikelsabido

Reputation: 1327

How to make a CASE statement dynamic in a MySQL Statement/Query

I have a table containing age, gender, sid, and goalname. I want to group this by age/gender and then display the value of the top 2 most used goalnames by each age/gender.

Here's the sample table:

Sample Table MySQL Table 1

I have managed to get an output using the query:

SELECT age, gender, 
SUM(CASE WHEN sid = 'Frank.net Life Cover' THEN 1 ELSE 0 END) AS 'Frank.net Life Cover',
SUM(CASE WHEN sid = 'Frank.net Salary Protection' THEN 1 ELSE 0 END) AS 'Frank.net Salary Protection',
SUM(CASE WHEN sid = 'Frank.net Hospital Cash Back' THEN 1 ELSE 0 END) AS 'Frank.net Hospital Cash Back' 
FROM (SELECT uid, sid, goalname, gender, age 
      FROM sampletable 
      WHERE age >=16 
      AND age < 100 
      GROUP BY age, gender);

Here's the output:

Sample MySQL Table 2

My desired output is somewhat like this but the thing about this is that it's not dynamic. New goalnames are added and this won't be applicable since I manually put the goalnames in the CASE statement.... Also, I just need to display the top 2 most used goalnames for each age/gender.

Note: It's okay if you couldn't display the top 2 and just display the total of each goalnames instead. I think I can just get the top two by comparing the totals of each goalname in PHP. I just need to have a dynamic SQL query.

Thank you!

Upvotes: 0

Views: 2813

Answers (2)

Kickstart
Kickstart

Reputation: 21513

The problem is that you are going to land up with a varying number of columns, which is messy to process (and is probably easier to bring each back as a different row anyway)

It would be possible using php to dynamically build up a query, although I wouldn't want to do this in a live situation:-

<?php 

$sql = "SELECT DISTINCT goalname FROM sampletable";

if ($result = $mysqli->query($sql)) 
{
    $sum_col = array();
    while ($row = $result->fetch_row()) 
    {
        $sum_col[] = "SUM(CASE WHEN goalname= '".$mysqli->real_escape_string($row['goalname'])."' THEN 1 ELSE 0 END) AS '".$mysqli->real_escape_string($row['goalname'])."'"
    }

    if (count($sum_col) > 0)
    {
        $sql = "SELECT age, 
                    gender, 
                    ".implode(",", $sum_col)."
                    FROM  sampletable 
                    WHERE age >=16 
                    AND age < 100 
                    GROUP BY age, gender";

        if ($result = $mysqli->query($sql)) 
        {
        }   
    }
}
?>

However I would suggest it would be easier to just do a simple grouped select and sort the formatting out afterwards:-

SELECT age, 
    gender, 
    goalname,
    COUNT(*)
FROM  sampletable 
WHERE age >=16 
AND age < 100 
GROUP BY age, gender, goalname

or if you need every goal name for every person, irrespective of whether they selected it or not:-

SELECT age, 
    gender, 
    sub0.goalname,
    COUNT(sampletable.sid)
FROM  (SELECT DISTINCT goalname FROM sampletable) sub0
LEFT OUTER JOIN sampletable
ON sub0.goalname = sampletable.goalname
WHERE age >=16 
AND age < 100 
GROUP BY age, gender, sub0.goalname

Getting the top 2 is a bit more complicated, and just about logically impossible with your current output (as the columns would be referring to varying rows)

EDIT - getting the highest 2 couple be done like this, but brining back both counts in 1 column (separated by ## - change this to something that your goal name can never contain):-

SELECT age, gender, SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS('=', goalname, goalcount) ORDER BY goalcount DESC SEPARATOR '##'), '##', 2)
FROM
(
    SELECT age, 
        gender, 
        goalname,
        COUNT(sid) AS goalcount
    FROM user
    WHERE age >=16 
    AND age < 100 
    GROUP BY age, gender, goalname
) sub1
GROUP BY age, gender;

SQL fiddle for this:-

http://sqlfiddle.com/#!9/9cc8f/4

Upvotes: 1

sai
sai

Reputation: 119

It is better to split the table by normalization.And put the sid,goalname in to another table and use the sid as the foreign key in the main table.This way you can check the values dynamically from the other table rather than hardcoding them.

SELECT s.age, s.gender, count(s.sid),p.goalname 
FROM sampletable s,proposedtable p
where s.sid=p.sid and s.age >=16 AND s.age < 100
group by s.age,s.gender

Upvotes: 0

Related Questions