Query optimisation

[Jonob]

Assume the following (simplified) tables:

 

person

id

name

 

sales

id

person_id (FK to person.id)

amount

is_deleted (bool, default = 0)

 

Transactions are stored in the sales table, some of which can be 'deleted' (i.e. sales.is_deleted = 1).

 

I want to create a sum of all sales (including deleted), and group by person

 

SELECT person, sum(sales.amount)
FROM sales
LEFT JOIN person ON sales.person_id = person.id
GROUP BY sales.person_id

 

This works great, and returns each person with their respective sales amount, even if the amount is null. Something like:

-Peter, 100

-Sally, 120

-Simon, null

 

However, as soon as I insert a WHERE clause on the is_deleted column, then it only returns data for each person where the amount is not null. I change the query to

 

SELECT person, sum(sales.amount)
FROM sales
LEFT JOIN person ON sales.person_id = person.id
WHERE sales.is_deleted=0
GROUP BY sales.person_id

and the data returned is as follows (i.e. Simon is missing)

-Peter, 80

-Sally, 100

 

 

I'd like the second query to return the exact same data as the first query, with the exception that some transactions will not be included in the sum, but this isnt working as expected. Strangely enough, i get the same (wrong) result by the following sql:

SELECT person, sum(sales.amount)
FROM sales
LEFT JOIN person ON sales.person_id = person.id
WHERE sales.is_deleted=0 OR sales.is_deleted=1
GROUP BY sales.person_id

 

Why are 'nulls' removed by the seemingly unrelated 'where' clause? I'm missing something obvious here.

 

Any help greatly appreciated.

[kickstart]

Hi

 

The WHERE is exlcuding them at a later stage.

 

Put the clause in the ON clause:-

 

SELECT person, sum(sales.amount)
FROM sales
LEFT JOIN person ON sales.person_id = person.id AND sales.is_deleted=0
GROUP BY sales.person_id

 

All the best

 

Keith

[Jonob]

Thanks for the reply Keith.

 

It seems to work insofar as all the records are returned...but it doesnt sum correctly - it appears that the 'AND sales.is_deleted=0' is completely ignored in the LEFT JOIN and that all records are summed (irrespective of whether is_deleted=1 or 0)

[kickstart]

Hi

 

Hmm, not certain.

 

However as I presume that you have a single person for multiple sales, and you want a person listed even if they have no sales then the join should be the other way round.

 

SELECT person, sum(sales.amount)
FROM person
LEFT JOIN sales ON person.id = sales.person_id AND sales.is_deleted=0
GROUP BY sales.person_id

 

All the best

 

Keith