mysql aggregate query returning wrong result

Question

I have data in two tables that i need to join and return the count of occurences of a record in one of the tables,

The data in Employee table looks like,

empId  workpatternId
    1        20

The data in the workPattern tables looks like,

workpatternId  monday tuesday wednesday thursday friday saturday sunday
   20           ALL     ALL     ALL       ALL     NULL   NULL     ALL

The following query should return 5, which is the count of ALL, but returns 7 instead,

SELECT empId,b.workingPatternId, COUNT(monday='ALL') +
 COUNT(tuesday='ALL') + COUNT(wednesday='ALL')+ COUNT(thursday='ALL') + 
    COUNT(friday='ALL')+ COUNT(saturday='ALL')+ COUNT(sunday='ALL') AS COUNT
      FROM workPattern b 
 join Employee e on (e.workpatternId = b.workpatternId) and e.empId = 1
         GROUP BY empId ;

what is wrong with the query?

EDIT

dbfiddle

Seriously consider revising your schema. A database table is NOT a spreadsheet — Strawberry, Oct 20 '20 at 08:46
You can't use COUNT for this. Use `SUM` with `ISNULL` function instead. — Jsowa, Oct 20 '20 at 08:48
Check this answer: https://stackoverflow.com/questions/8732036/mysql-sum-query-with-if-condition — Jsowa, Oct 20 '20 at 09:13
@Tajni replacing the count with sum seems to have resolved it — Bisoux, Oct 20 '20 at 09:14

GMB · Accepted Answer · 2020-10-20T17:10:50.093

1

I don't see the point for aggregation, since it seems you have just one row in workPattern per empId. You could just write this as:

SELECT e.empId, wp.workingPatternId, 
      (wp.monday    = 'ALL') 
    + (wp.tuesday   = 'ALL') 
    + (wp.wednesday = 'ALL') 
    + (wp.thursday  = 'ALL') 
    + (wp.friday    = 'ALL') 
    + (wp.saturday  = 'ALL') 
    + (wp.sunday    = 'ALL') cnt
FROM workPattern wp
INNER Employee e on e.workpatternId = wp.workpatternId 
WHERE e.empId = 1

If you need aggregation for some reason, then you want sum() rather than count(): the latter counts all non-null values, while a false condition is evaluated as 0 (which is not null, so it is taken into account in your query):

SELECT 
    SUM(
          (wp.monday    = 'ALL') 
        + (wp.tuesday   = 'ALL') 
        + (wp.wednesday = 'ALL') 
        + (wp.thursday  = 'ALL') 
        + (wp.friday    = 'ALL') 
        + (wp.saturday  = 'ALL') 
        + (wp.sunday    = 'ALL')
    ) cnt
FROM workPattern wp
INNER JOIN Employee e on e.workpatternId = wp.workpatternId 
WHERE e.empId = 1

edited Oct 20 '20 at 17:10

answered Oct 20 '20 at 10:34

GMB

216,147
25
84
135

Thank you. The data i provided is a snippet, can't possibly post all due to the volume, so yes aggregation is needed. – Bisoux Oct 20 '20 at 11:13
I think you need to edit the aggregation sql. cnt is showing 1, should be 5 – Bisoux Oct 20 '20 at 11:20
@Bisoux: I don't see how the second query can return `1`. It returns a value that is equal to, or greater than, the value returned by the first query. – GMB Oct 20 '20 at 12:12
I tested it and it returns 1. The first and second query are not directly related. I have posted what worked as an answer. – Bisoux Oct 20 '20 at 12:14
@Bisoux: it would help if you could set up a [db-fiddle](https://dbfiddle.uk/) of some sort. – GMB Oct 20 '20 at 12:15
I have added a dbfiddle link to the question – Bisoux Oct 20 '20 at 16:54
1

@Bisoux: ah yes, you were right... The conditions need to be surrounded with parentheses. I fixed the answer. I should have spotted that by myself in the first place, but I learnt something here! – GMB Oct 20 '20 at 17:12

score 0 · Answer 2 · edited Oct 20 '20 at 17:12

0

This worked:

 SELECT empId,b.workingPatternId, sum(Monday='ALL') + sum(Tuesday='ALL') + sum(Wednesday='ALL')+ sum(Thursday='ALL') + sum(Friday='ALL') + sum(Saturday='ALL')+ sum(Sunday='ALL') AS COUNT
 FROM WorkPatterns b 
 JOIN Employee e 
        ON (e.workingPatternId = b.workingPatternId) 
        AND e.empId = 1   
 GROUP BY empId ;

edited Oct 20 '20 at 17:12

GMB

216,147
25
84
135

answered Oct 20 '20 at 11:57

Bisoux

532
6
18

mysql aggregate query returning wrong result

2 Answers2