How to count without duplicates in two columns?

Question

I have table:

+----------------+
| table          |
+----------------+
| u_id | sail_id |
+----------------+
| 1    | 5       |
| 1    | 5       |
| 2    | 5       |
| 2    | 4       |
| 1    | 4       |
+----------------+

How to write sql statement to count different u_id with different sail_id (means no duplicate)?

Example: if SELECT COUNT(*) FROM table GROUP BY sail_id, result will be 2

if SELECT COUNT(*) FROM table GROUP BY sail_id, user_id, result will be 1

I need result to be 4 (because there are 5 rows and only first and second rows have same u_id and sail_id). Maybe I need add somewhere DISTINCT.

Answer 1

1) You can use COUNT(DISTINCT ...):

SELECT COUNT(DISTINCT u_id,sail_id)
FROM tab;

SqlFiddleDemo

2) You can use subquery with DISTINCT:

SELECT COUNT(*)
FROM (SELECT DISTINCT u_id, sail_id
      FROM table) AS sub;

LiveDemo

3) You can use subquery with GROUP BY:

SELECT COUNT(*)
FROM (SELECT u_id, sail_id
      FROM table
      GROUP BY u_id, sail_id) AS sub;

4) Last possibility is to use:

SELECT COUNT(DISTINCT CONCAT(u_id,',',sail_id))
FROM table;

SqlFiddleDemo