mysql: column values as column name in select result

Question

i have a table which i group by one column and then i output the different values and the count of this values.

activty | sum
-------------
Form    |  1
Login   |  4
Reg     |  3

here is an example-code: http://sqlfiddle.com/#!2/c6faf/2/0

but i want the different values tp the column names with one row of values (the count).

like this:

Form | Login | Reg
------------------
  1  |   4   |  3

i tried the PIVOT operation, but i'm not getting it working. what am i doing wrong?

here is my code: http://sqlfiddle.com/#!2/c6faf/35/0

thanks in advance!

br

i've put a fiddle in my response so you can check it out

Stephan
– Stephan

2013-06-07 08:57:03 +00:00
Commented Jun 7, 2013 at 8:57 — Stephan
– Stephan, Commented Jun 7, 2013 at 8:57

fancyPants · Accepted Answer · 2013-06-07 08:34:28Z

2

Here you go

SELECT 
MAX(CASE activity WHEN 'Form' THEN mysum ELSE NULL END) AS Form,
MAX(CASE activity WHEN 'Login' THEN mysum ELSE NULL END) AS Login,
MAX(CASE activity WHEN 'Reg' THEN mysum ELSE NULL END) AS Reg
FROM (
SELECT activity, COUNT(activity) AS mysum FROM tbl_tracking
            WHERE id = 141 AND variant = 2
            GROUP BY activity
)sq

But in my opinion such formatting should be done in application layer, not in database layer.

answered Jun 7, 2013 at 8:34

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

swapnesh Over a year ago

+1 for " formatting should be done in application layer, not in database layer."

Stephan · Accepted Answer · 2013-06-07 08:56:26Z

1

Try this:

SELECT  
    SUM(IF(activity = 'Form',1,0)) as 'Form',
    SUM(IF(activity = 'Login',1,0)) as 'Login',
    SUM(IF(activity = 'Reg',1,0)) as 'Reg'
FROM 
    tbl_tracking
WHERE 
    id = 141 
    AND variant = 2

Sql Fiddle here

edited Jun 7, 2013 at 8:56

answered Jun 7, 2013 at 8:34

Stephan

8,1103 gold badges40 silver badges42 bronze badges

Comments

Ganesh Bora · Accepted Answer · 2013-06-07 09:05:59Z

0

For this you need to use pivot table concept. here you go for same.

SELECT
  SUM((IF(activity='Form',1,0))) as Form,
  SUM((IF(activity='Login',1,0))) as Login,
  SUM((IF(activity='Reg',1,0))) as Reg
FROM 
  tbl_tracking
WHERE 
  id = 141 
  AND 
  variant = 2;

fiddle

answered Jun 7, 2013 at 9:05

Ganesh Bora

1,1539 silver badges18 bronze badges

Collectives™ on Stack Overflow

mysql: column values as column name in select result

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related