mysql count sum code example

Example 1: mysql count vs sum

+----+------+
| id | vote |
+----+------+
|  1 |    1 |       COUNT(vote) = 5 
|  2 |   -1 |		SUM(vote) = 1  = (-2 + 3 = 1)
|  3 |    1 |			  
|  4 |   -1 |	#Sum is doing the mathematical sum, whereas count simply 
|  5 |    1 |	#counts any value as 1 regardless of what data type.
+----+------+

/**
The first query returns the number of times the condition is true, because true is 1 and false is 0.
The second query returns the complete record count because count() does not care about the content inside it, as long as the content is NOT NULL. Because count(1) and count(0) are still values and both get counted.
To get the correct return value for the second query you would have to make the result of the condition be null (instead of 0) to not being counted. Like this:
*/
SELECT COUNT(case when USER_NAME = 'JoeBlow' then 'no matter what' else NULL end) 
from your_table

Example 2: using sum function in mysql for two diffrenet conditions

SELECT 
    SUM(CASE
        WHEN status = 'Shipped' THEN quantityOrdered
    END) qty_shipped,
    SUM(CASE
        WHEN status = 'Resolved' THEN quantityOrdered
    END) qty_resolved,
    SUM(CASE
        WHEN status = 'Cancelled' THEN quantityOrdered
    END) qty_cancelled,
    SUM(CASE
        WHEN status = 'On Hold' THEN quantityOrdered
    END) qty_on_hold,
    SUM(CASE
        WHEN status = 'Disputed' THEN quantityOrdered
    END) qty_on_disputed,
    SUM(CASE
        WHEN status = 'In Process' THEN quantityOrdered
    END) qty_in_process
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber);

Example 3: sum mysql column

SELECT sum( mark ) FROM `student`