Join multiple lines to one
I have a problem combining the results of multiple rows into one single row result by user id.
I get this result:
||userID||eventTypeID||COUNT(*)||
|| 1 || 1 || 1 ||
|| 1 || 2 || 1 ||
|| 2 || 1 || 1 ||
|| 3 || 2 || 1 ||
I would like it to turn out like this:
||userID||eventTypeID ONE||COUNT()||eventTypeID TWO||COUNT()||
etc.
I can't figure out how I do it, I've tried pivotSQL and concat, but I can't seem to get it to work.
My SQL query:
SELECT eventMatch.userID, eventMatch.eventTypeID, COUNT( * )
FROM `eventMatch` , activity, activityMatch
WHERE eventMatch.activityID = activity.activityID
AND activity.activityID = activityMatch.activityID
AND activity.khID =1
GROUP BY eventTypeID, userID
ORDER BY userID ASC
Hope someone can help, maybe this is easy, but I tried looking for solutions for 2 days :-)
Thank..
source to share
Try the following:
SELECT
eventMatch.userID,
SUM(eventMatch.eventTypeID = 1) As TypeOne,
SUM(eventMatch.eventTypeID = 2) As TypeTwo,
COUNT( * )
FROM `eventMatch`
INNER JOIN activity ON eventMatch.activityID = activity.activityID
INNER JOIN activityMatch ON activity.activityID = activityMatch.activityID
WHERE activity.khID =1
GROUP BY userID
ORDER BY userID ASC;
Update 1
For several types of events, you can do it dynamically like this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ''',
em.eventTypeID, ''', 1, 0)) AS Type',
em.eventTypeID )
) INTO @sql
FROM `eventMatch` AS em;
SET @sql = CONCAT('SELECT em.userID, ', @sql,
', COUNT( * )
FROM `eventMatch` AS em
INNER JOIN activity AS a ON em.activityID = a.activityID
INNER JOIN activityMatch AS am ON a.activityID = am.activityID
GROUP BY em.userID
ORDER BY em.userID ASC ;');
prepare stmt
FROM @sql;
execute stmt;
SQL Fiddle Demo
Update 2
If you want to exclude all types from the table EventTypes
, even if this type does not have matches in the other table, you should get the list of types dynamically in the first step from this table EventTypes
instead of getting them from the table eventmatch
, execute the dynamic query in the same way as before. Like this:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(em.eventTypeID = ''',
em.eventTypeID, ''', 1, 0)) AS Type',
em.eventTypeID )
) INTO @sql
FROM eventTypes AS em; -- <---------- this is what I changed.
SET @sql = CONCAT('SELECT em.userID, ', @sql,
', COUNT( * )
FROM `eventMatch` AS em
INNER JOIN activity AS a ON em.activityID = a.activityID
INNER JOIN activityMatch AS am ON a.activityID = am.activityID
GROUP BY em.userID
ORDER BY em.userID ASC ;');
prepare stmt
FROM @sql;
execute stmt;
Updated version of SQL Fiddle
source to share