CODEWITHSUNDEEP
mysqlsql
The Stompiest
The Stompiest

Reputation: 331

SQL query ignores WHERE clausule and gives strange result

I have two tables. One is called Sales and the other one is called Uren. Each entry in the Sales table represents one sale with the date (finalized_at) when the sale has been made and each entry in the Uren table represents a shift worked by the agent containing the hours worked and the date (datum) when the shift was. The tables look like this:

Sales table

+-------+--------------+
| agent | finalized_at |
+-------+--------------+
| John  | 01-01-2020   |
+-------+--------------+
| John  | 02-01-2020   |
+-------+--------------+
| Mark  | 01-01-2020   |
+-------+--------------+
| Peter | 01-01-2020   |
+-------+--------------+
| John  | 04-01-2020   |
+-------+--------------+

Uren table

+-------+-------+------------+
| agent | hours | datum      |
+-------+-------+------------+
| John  | 1     | 01-01-2020 |
+-------+-------+------------+
| John  | 5     | 02-01-2020 |
+-------+-------+------------+
| Mark  | 1     | 01-01-2020 |
+-------+-------+------------+
| Peter | 2     | 01-01-2020 |
+-------+-------+------------+
| John  | 4     | 04-01-2020 |
+-------+-------+------------+

In order to calculate the commission of the agents on the 4th of january I have to take in account the performance of the agent of the rest of the month. In order to do that I need to calculate the sales per hour of the whole month, but only of the agents who made a sale on the 4th of january.

My query looks like this:

SELECT 
  agent, 
  (SELECT COUNT(*) 
   FROM Sales 
   WHERE agent=Sales.agent 
     AND finalized_at BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
  ) / (
   SELECT SUM(hours) 
   FROM Uren 
   WHERE datum BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' 
     AND agent=Sales.agent
  ) as sph 
FROM Sales 
WHERE finalized_at BETWEEN '2020-01-04 00:00:00' AND '2020-01-04 23:59:59' 
GROUP BY agent 
ORDER by sph DESC;

For some reason when running the query the second column (sales) returns the total amount of lines in the Sales table instead of using the where clausule as stated.

So my question is. What am I doing wrong?

Upvotes: 1

Views: 49

Answers (1)

dcp
dcp

Reputation: 55449

Try using a table alias (Sales2 is what I used below) inside the inline view to make sure it's referring to the correct table.

SELECT 
  agent, 
  (SELECT COUNT(*) 
   FROM Sales Sales2 
   WHERE Sales2.agent=Sales.agent 
     AND Sales2.finalized_at BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59'
  ) / (
   SELECT SUM(uren) 
   FROM Uren 
   WHERE datum BETWEEN '2020-01-01 00:00:00' AND '2020-01-31 23:59:59' 
     AND agent=Sales.agent
  ) as sph 
FROM Sales 
WHERE finalized_at BETWEEN '2020-01-04 00:00:00' AND '2020-01-04 23:59:59' 
GROUP BY agent 
ORDER by sph DESC;

Also, be careful about a possible divide by 0 if there aren't any associated records in Uren table.

Upvotes: 2

Related Questions