Counting distinct rows in MySQL and group the result while omitting dupes and null result-CodePudding

I am trying to query the database but i need help getting it right. Have two issues.

Database is for hamradio contesting and booth querys applies on the same table.

Table "LOG" with columns: MCall, SCall, sm, mm, conf where MCall and SCall is unique per user but exists in multiple rows sm and mm is an idenifier number for areas conf 1 or 0 depending on if the data is valid or not

LOG containing

MCall   SCall   sm    mm    Conf
SM6ASD  ADMIN   2422  2462  1   
SM6ASD  ADMIN   2422        0
ADMIN   SM5QWE  2321        0
ADMIN   SM1ZXC  1766        1
SM6ASD  ADMIN   1860        1
SM6ASD  SM6ABC  2584  2510  1
SM6ASD  SM6ABC  2161        1
SM6ASD  SM6ABC  1785        1
SM6ASD  ADMIN   0114        1
SM6ASD  ADMIN   1230  1496  1
SM6ASD  ADMIN   1230  1230  1

Issue 1

Calculate a score for each MCall. Score calculated by this:

A row with "Conf" = 0 gives 0 points
A row with "Conf" = 1 and "sm" set gives 1 point
A row with "Conf" = 1 and "mm" set gives 1 point
A row with "Conf" = 1 and "sm" or "mm" set gives 2 points
A row with "Conf" = 1 and "sm" = "mm" set gives 1 point

Eg 1:st row SM6ASD gives a score of 2 points 2:nd row SM6ASD gives a score of 0 points Last row SM6ASD gives a score of 1 points

This query will count 2 points for sm != mm. How can i omit that?

SELECT distinct MCall, sm, mm, SUM(IF(sm != '', 1,0))   SUM(IF(mm != '', 1,0)) AS 'points' 
FROM LOG WHERE conf = '1' GROUP BY `MCall` ORDER BY `points` DESC

Issue 2 Need to count distinct occurrence of "sm" and "mm" per user (MCall) The result shall be a descending list of unique identifiers from "sm" and "mm"

Scoring should be: SM6ASD 10 areas (distinct) ADMIN 1 area

Have tried many querys but none good enough SELECT COUNT(DISTINCT sm, mm) AS areas, MCall FROM LOG WHERE conf = "1" GROUP BY MCall

gives wrong ammount. I think the query counts one NULL occurence!?

SELECT sm FROM LOG UNION SELECT mm FROM LOG WHERE MCall = "SM6ASD" AND conf = "1" will list the right answer but how can i count the rows instead, and at the same time group the result per user

eg SM6ASD 10 areas ADMIN 1 areas

Hoping for some help :)

CodePudding user response：

Issue 2 solved by..

SELECT distinct mycall, sm, mm, SUM(IF(sm = mm, 1,0))   SUM(IF(sm != mm, 0,0))   SUM(IF((sm != mm) AND (sm = ''), 1,0))   SUM(IF((sm != mm) AND (mm = ''), 1,0))   SUM(IF((sm != mm) AND (mm != '') AND (sm != ''), 2,0)) AS 'points'
FROM KJ_log WHERE conf = '1'
GROUP BY `mycall`
ORDER BY `points` DESC

CodePudding user response：

SELECT mycall, count(conf) 
FROM KJ_log 
WHERE ((sm != mm) OR (sm IS NOT NULL AND mm IS NOT NULL) OR (mm IS NOT NULL AND sm IS NOT NULL)) and conf = "1" 
GROUP BY mycall

I'm The best :)