Usage of sum (if () in mysql

Original table:
Id fenlei time
1 Category 1 20130316
2 Category 2 20130316
3 Category 3 20130317
4 category 2 20130317
5 Category 3 20130318

You need to check the table above and insert the result to the new table.
New table structure:

Id fenlei_1 fenlei_2 fenlei_3 date
1 1 1 0 20130316
2 0 1 20130317
3 0 0 1 20130317

You need to obtain the number of messages for each category on a daily basis.

Select time, sum (if (fenlei = 'classification 1', 1, 0 )),
Sum (if (fenlei = 'classification 2',), sum (if (fenlei = 'classification 3 ))
From tt
Group by time

More methods reference: http://blog.csdn.net/lifuxiangcaohui/article/details/6861570

First, a simple sum

Select sum (qty) as total_qty from inventory_product group by product_id

This will calculate the qty of all products.

Unfortunately, the qty value in our system is negative. I just want to count the qty values of the positive values, and add the if function. SQL:

Select sum (if (qty> 0, qty, 0) as total_qty from inventory_product group by product_id

If the value of qty is greater than 0, the value of qty is accumulated to total_qty; otherwise, the value is accumulated to total_qty.

Further enhanced:

Select
Sum (if (qty> 0, qty, 0) as total_qty,
Sum (if (qty <0, 1, 0) as negative_qty_count
From inventory_product
Group by product_id

In this way, we can count the number of negative records. The convenience program is used to tell people that this record data is abnormal.