CODEWITHSUNDEEP
mysqlsqlsumaverage
Shahid Pathan
Shahid Pathan

Reputation: 225

MySQL functions COUNT/MIN/MAX issue

I run following queries for sum then I get exactly the same answer

SQL 1 ::

Select L_PARTKEY,sum(L_LINENUMBER) As A 
From lineitem 
Inner Join orders On orders.O_ORDERKEY = lineitem.L_ORDERKEY  
WHERE O_WEEKDAY='Tuesday' 
Group by L_PARTKEY

SQL 2 ::

SELECT sum(IF(orders.O_WEEKDAY='Tuesday',L_LINENUMBER, 0)) As 'Tuesday' 
FROM lineitem 
INNER JOIN orders ON orders.O_ORDERKEY = lineitem.L_ORDERKEY
GROUP BY  lineitem.L_PARTKEY

But when I run the above queries with AVG/COUNT/MIN/MAX function instead of SUM then both queries gives different output.

Upvotes: 2

Views: 173

Answers (1)

Andomar
Andomar

Reputation: 238246

Say you have the following data:

monday    1
tuesday   2
tuesday   3

Your first query looks at the last two rows. The sum is 5, the average 2.5, the number of lines is 2.

Your second query looks at all rows. For the first row, it replaces the number 1 with 0. The sum is still 5, but the average is now 5/3, and the number of lines is 3.

For the sum, extra rows with a value of 0 do not matter. But extra rows with 0 do influence the other aggregates count, min, max and avg.

Upvotes: 2

Related Questions