CODEWITHSUNDEEP
mysqlsql
IsraGab
IsraGab

Reputation: 5185

MySQL group field with their values

Assume that I have a table call client. In this table I have 3 fields (A, B, C) I would like to group each rows when values of A = B = C ex:

A   B   C    otherRow
1   2   3    x 
2   1        x 
4   2        y

I would like to get the folowing

(A,B,C)   otherRow  Count
    1     x         2
    2     x         2
    2     y         1
    3     x         1
    4     y         1

Upvotes: 0

Views: 46

Answers (1)

Alma Do
Alma Do

Reputation: 37365

Your query is UNION, not JOIN:

SELECT 
  `A,B,C`, 
  otherRow, 
  COUNT(`A,B,C`) AS `Count`
FROM
  (SELECT a AS `A,B,C`, otherRow FROM t
  UNION ALL
  SELECT b AS `A,B,C`, otherRow FROM t
  UNION ALL
  SELECT c AS `A,B,C`, otherRow FROM t) AS u
GROUP BY
  `A,B,C`,
  otherRow
HAVING
  `A,B,C` IS NOT NULL

check this fiddle. I've added NULL-check since it's not obvious what are your "empty" values. If you'll remove it, you'll get zero-count NULL-rows.

Upvotes: 3

Related Questions