-1

I have a mapping table like below

c1  c2
--  --
1   1
1   1
1   2
1   3
1   3
2   1
2   2
2   3
3   1

and so on. The table has a separate id column (not showed here). Here is my query so far : 

SELECT `c1`, `c2`, COUNT(*) AS `count_of_uniques` FROM `map_table` 
GROUP BY `c1`, `c2`

I have also tried with distinct query like this.

SELECT `c1`, `c2`, COUNT(DISTINCT `c1`, `c2`) AS `count_of_uniques` FROM `map_table`

The expected result is

c1  c2  count_of_uniques
--  --  ----------------
1   1   2
1   2   2
1   3   3
2   2   1
2   3   1

My current query shows the correct output when the combination is made of two similar numbers, but when we have something like 1-2 and 2-1, the query does not have the correct output.

distinct shows still fewer results.

Any help is greatly appreciated. Thank you.

Sirmyself
  • 1,464
  • 1
  • 14
  • 29
Prashanth Benny
  • 1,523
  • 21
  • 33
  • 2
    Your expected output [exactly matches](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c0b6432f881d1fbe6746a3efad13179) the actual output of your first query. Have you posted the wrong expected output? – GarethD May 28 '19 at 13:23
  • is it SQL or MySQL? – Sirmyself May 28 '19 at 13:32
  • *"is it SQL or MySQL?"* @Sirmyself the [sql](https://stackoverflow.com/questions/tagged/sql) tag is not about SQL Server (MSSQL) on this website if that is what you are thinking.. – Raymond Nijland May 28 '19 at 13:42
  • 1
    @raymond-nijland I stand corrected then – Sirmyself May 28 '19 at 13:44
  • @GarethD I am sorry for the wrong data. I tried to make thinds simple but loosing lot of detail. [Here is the actual problem](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d8ce0ffba7a58bc6e74d3bd83de8cdb4) – Prashanth Benny May 29 '19 at 05:13

3 Answers3

3

I think you might be after something that deals with combinations rather than permutations i.e 1, 2 and 2, 1 should be treated as the same combination. If this is the case you can use a case expression to make sure that c1 is always the lower of the two values, and c2 is the higher of the two. This will group similar pairs together (so 2, 1 will first be reversed to become 1, 2 then grouped with all similar results):

SELECT c1, c2, COUNT(*) AS `count_of_uniques` 
FROM (  SELECT CASE WHEN c1 > c2 THEN c2 ELSE c1 END AS c1, 
                CASE WHEN c1 > c2 THEN c1 ELSE c2 END AS c2
        FROM map_table) AS t
GROUP BY c1, c2

Output

c1      c2      count_of_uniques
-------------------------------
1       1       2
1       2       2
1       3       3
2       2       1
2       3       1

Example on DB Fiddle

GarethD
  • 68,045
  • 10
  • 83
  • 123
  • 1
    Best answer I think. Question though: what would be more efficient between using the `IF` function and using the `CASE` statement? – Sirmyself May 28 '19 at 13:45
  • The question shows a case *expression*, not a case statement (the difference is often me being pedantic, but in MySQL it is more important as MySQL has both a [case statement](https://dev.mysql.com/doc/refman/5.7/en/case.html) and a [case expression](https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case)). Nonetheless, [this has been asked before](https://stackoverflow.com/questions/42527259) and there was no conclusive answer. Realistically I would expect any performance difference to be so negligible they can assumed to be equal. – GarethD May 28 '19 at 14:03
  • I know SQL Server a lot better than MySQL and I know the SQL Server equivalent to `IF()` (`IIF()`) is rewritten during compilation as a CASE expression anyway, so there is absolutely zero performance difference. I would expect MySQL to take advance of a similar operation, so that internally both are performing exactly the same operation. – GarethD May 28 '19 at 14:04
1

You can use the functions least() and greatest() to get the pairs on which you will group:

select 
  least(c1, c2) c1, 
  greatest(c1, c2) c2, 
  count(*) count_of_uniques
from map_table  
group by 
  least(c1, c2), 
  greatest(c1, c2)

See the demo.
Results:

| c1  | c2  | count_of_uniques |
| --- | --- | ---------------- |
| 1   | 1   | 2                |
| 1   | 2   | 2                |
| 1   | 3   | 3                |
| 2   | 2   | 1                |
| 2   | 3   | 1                |
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Select C1,C2,Count(*) from map_table Group By C1,C2 Order by C1