I have a table of survey data where lines of results are separated into many rows by, each having their own variable name.
My table looks like this:
data_id data_content var_name var_line
1 1 SERIAL 1
2 2 GND.AGE 1
3 3 GND.NEWS.FREQ 1
4 2 SERIAL 2
5 3 GND.AGE 2
6 3 GND.NEWS.FREQ 2
7 3 SERIAL 3
8 3 GND.AGE 3
9 4 GND.NEWS.FREQ 3
Here is my current query to retrieve the total number of answers for every possible answer of GND.NEWS.FREQ. As in the total number:
SELECT *, COUNT(*) as total
FROM `data`
WHERE `var_name` = 'GND.NEWS.FREQ'
GROUP BY `data_content`
Now I need to add the functionality to only return answers where GND.AGE for example is 3. So basically treat all rows where var_line = 1 as a single row.
I have looked up pivot tables but I'm not sure how to add that into my current query.
I would like to do this in one query if possible but I wouldn't mind doing something like getting var_line IDs in a separate query.
Unfortunately MySQL does not have a PIVOT function which is basically what you are trying to do. So you will need to use an aggregate function with a CASE statement. If you have a unknown number of var_name values that you want to turn into columns, then you can use prepared statements:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(case when var_name = ''',
var_name,
''' then var_line end) AS ',
replace(var_name, '.', '_')
)
) INTO @sql
FROM data;
SET @sql = CONCAT('SELECT data_content,', @sql, '
from data
group by data_content
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;