Difference between SUM() and COUNT() in MySQL-CodePudding

I was solving this Leetcode question(1173. Immediate Food Delivery I) and I am kinda confused on why I am not getting the right answer when using COUNT(). but when I use SUM(), I am getting the right answer. I specify a condition for both SUM and COUNT and from my understanding, they should both be adding or counting when order_date=customer_pref_delivery_date, is that mean the condition only working for SUM()?

Query that return the right answer(33.33):

select round(sum(order_date=customer_pref_delivery_date)/count(delivery_id)*100,2) as immediate_percentage 
from Delivery

Query that return the wrong answer(100.00)

select round(count(order_date=customer_pref_delivery_date)/count(delivery_id)*100,2) as immediate_percentage 
from Delivery

CodePudding user response：

So basically SUM() function will return the sum of the expression inside of it and COUNT() will returns the number of non-NULL values of elements or rows that the query have returned.

In this case with your query you are looking to get the percentage of the immediate delivery orders so your formula to implement this is ((<numberofimmediate>/<totalamount>)*100). At first you can think that COUNT() would be the right approach, but that actually will return the complete number of elements in your query so to avoid this we will use SUM() to just acumulate the 'ones' that complete the condition ( preferred and estimated are equals so will return 1). If you want to see this in a more visual way you can do:

SELECT delivery_id,order_date=customer_pref_delivery_date FROM Delivery

This will return the delivery id and 1 if the condition is true but 0 if its false. Then you can use both of functions to see what is the effect on each one:

SELECT COUNT(order_date=customer_pref_delivery_date) FROM Delivery;
SELECT SUM(order_date=customer_pref_delivery_date) FROM Delivery;

COUNT()query will return, again, the complete amount of data present in the comparation (Counting 1 and 0) SUM()query will return the sum of all the values present in the comparation (Adding 1 and 0)

Hope this gives you a better understanding of what's going on with COUNT and SUM on your queries

You can check this fiddle

CodePudding user response：

Obviously, when you choose Count() it counts how many fields have numbers for example: we have 5 rows, every row has a number (random numbers, let's say 1,2,3,4,5) so the count here will be 5 while the Sum() gives you the sum of variables in this field (in our example: 1 2 3 4 5 = 15).

In the query that gives you the wrong answer, you simply divide count by count (for example 5/5 *100) which will always give you 1.

I hope that you understand