CODEWITHSUNDEEP
mysqlcountgroup-by
rupeshj
rupeshj

Reputation: 139

MySQL : Query and group by values in same column

I have a sql table data as below.

c_type_id | s_id | value
     1    | 11   | Winx32
     1    | 12   | Domain1
     2    | 11   | Winx64
     2    | 12   | Domain2
     3    | 11   | Winx32
     3    | 12   | Domain1
     4    | 11   | Winx32
     4    | 12   | Domain2

How to query and group to get the following result.?

countall | platform | domain
       2 | Winx32   | Domain1
       1 | Winx64   | Domain2
       1 | Winx32   | Domain2

Upvotes: 0

Views: 111

Answers (3)

speedbomb
speedbomb

Reputation: 1

assuming your table name is 'test1', this would be your solution:

SELECT COUNT(*) AS countall, t1a.value AS platform, t1b.value AS domain
FROM test1 AS t1a
LEFT JOIN test1 AS t1b
       ON t1b.c_type_id = t1a.c_type_id
WHERE t1a.s_id = 11
  AND t1b.s_id = 12
GROUP BY t1a.value, t1b.value

Tested on MySQL server v5.5.37

Best regards, speedbomb

Upvotes: 0

Barmar
Barmar

Reputation: 782436

You need to pivot the table to get the platform and domain in separate columns, then you can use COUNT and GROUP BY.

SELECT COUNT(*) AS countall, platform, domain
FROM (SELECT MAX(CASE WHEN s_id = 11 THEN value END) AS platform,
             MAX(CASE WHEN s_id = 12 THEN value END) AS domain
      FROM YourTable
      GROUP BY c_type_id) AS pivoted
GROUP BY platform, domain

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425793

SELECT  COUNT(*) countall,
        p.value platform,
        d.value domain
FROM    mytable p
JOIN    mytable d
ON      (d.c_type_id, d.s_id) = (p.c_type_id, 12)
WHERE   p.s_id = 11
GROUP BY
        d.c_type_id

Upvotes: 0

Related Questions