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
– Strawberry, Commented Oct 20, 2020 at 8:46
You can't use COUNT for this. Use SUM with ISNULL function instead. — JSowa
– JSowa, Commented Oct 20, 2020 at 8:48
@Tajni replacing the count with sum seems to have resolved it — Bisoux
– Bisoux, Commented Oct 20, 2020 at 9:14

GMB · Accepted Answer · 2020-10-20 17:10:50Z

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, 2020 at 17:10

answered Oct 20, 2020 at 10:34

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Bisoux Over a year ago

Thank you. The data i provided is a snippet, can't possibly post all due to the volume, so yes aggregation is needed.

Bisoux Over a year ago

I think you need to edit the aggregation sql. cnt is showing 1, should be 5

GMB Over a year ago

@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.

Bisoux Over a year ago

I tested it and it returns 1. The first and second query are not directly related. I have posted what worked as an answer.

GMB Over a year ago

@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 · Accepted Answer · 2020-10-20 17:12:58Z

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, 2020 at 17:12

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Oct 20, 2020 at 11:57

Bisoux

5346 silver badges22 bronze badges

Collectives™ on Stack Overflow

mysql aggregate query returning wrong result

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related