mysql sum function issue

Question

I have some doubt with the SUM() function in mysql. When i am using SUM() function without condition is will work fine. But if i will use SUM() function with condition it will return number of records that match the condition. Explain me this.

I was created one table Teacher with four column as below.

mysql> desc Teacher;


+--------------+-------------+------+-----+-------------------+
| Field        | Type        | Null | Key | Default           |
+--------------+-------------+------+-----+-------------------+
| TeacherId    | varchar(36) | NO   | PRI | NULL              |
| FirstName    | varchar(50) | NO   |     | NULL              |
| LastName     | varchar(50) | NO   |     | NULL              |
| SyncStatus   | int(11)     | NO   |     | NULL              |
+--------------+-------------+------+-----+-------------------+

It was contained following records.

mysql> select * from Teacher;

+-----------+-----------+-----------+------------+
| TeacherId | FirstName | LastName  | SyncStatus |
+-----------+-----------+-----------+------------+
| 001       | Sagar     | Kapadia   |          1 |
| 002       | Vishal    | Chaudhari |          1 |
| 003       | Milan     | Panchal   |          1 |
| 004       | Jugal     | Desai     |          1 |
| 005       | Baburao   | Apte      |          1 |
| 006       | Jaya      | Bacchan   |          0 |
| 007       | Jaya      | Bacchan   |          2 |
| 008       | Jaya      | Bacchan   |          2 |
+-----------+-----------+-----------+------------+
8 rows in set (0.00 sec)




mysql> select TeacherId,FirstName,LastName,Concat(FirstName,' ',LastName) as FullName,SUM(SyncStatus) from Teacher having FullName like '_____ %';
+-----------+-----------+----------+---------------+-----------------+
| TeacherId | FIrstName | LastName | FullName      | sum(SyncStatus) |
+-----------+-----------+----------+---------------+-----------------+
| 001       | Sagar     | Kapadia  | Sagar Kapadia |               9 |
+-----------+-----------+----------+---------------+-----------------+
1 row in set (0.00 sec)

So it will return me the perfect sum on sync status. But if i will use the SUM() with condition then returen the number of records.

mysql> select TeacherId,FIrstName,LastName,Concat(FirstName,' ',LastName) as FullName,sum(SyncStatus = 0) from Teacher having FullName like '_____ %';
+-----------+-----------+----------+---------------+---------------------+
| TeacherId | FIrstName | LastName | FullName      | sum(SyncStatus = 0) |
+-----------+-----------+----------+---------------+---------------------+
| 001       | Sagar     | Kapadia  | Sagar Kapadia |                   1 |
+-----------+-----------+----------+---------------+---------------------+
1 row in set (0.00 sec)



mysql> select TeacherId,FIrstName,LastName,Concat(FirstName,' ',LastName) as FullName,sum(SyncStatus = 1) from Teacher having FullName like '_____ %';
+-----------+-----------+----------+---------------+---------------------+
| TeacherId | FIrstName | LastName | FullName      | sum(SyncStatus = 1) |
+-----------+-----------+----------+---------------+---------------------+
| 001       | Sagar     | Kapadia  | Sagar Kapadia |                   5 |
+-----------+-----------+----------+---------------+---------------------+
1 row in set (0.00 sec)



mysql> select TeacherId,FIrstName,LastName,Concat(FirstName,' ',LastName) as FullName,sum(SyncStatus = 2) from Teacher having FullName like '_____ %';
+-----------+-----------+----------+---------------+---------------------+
| TeacherId | FIrstName | LastName | FullName      | sum(SyncStatus = 2) |
+-----------+-----------+----------+---------------+---------------------+
| 001       | Sagar     | Kapadia  | Sagar Kapadia |                   2 |
+-----------+-----------+----------+---------------+---------------------+
1 row in set (0.00 sec)

score 3 · Accepted Answer · edited Jan 08 '13 at 12:50

3

You are right. This function sum(SyncStatus = 2) will return the number of rows, because that is how it is executed

In each row SyncStatus = 2 will test if SyncStatus equals 2 and returns 1(true) or 0(false), so you are basically getting the sum of the truth values(1)

edited Jan 08 '13 at 12:50

Strawberry

33,750
13
40
57

answered Jan 08 '13 at 12:46

Ankur

12,676
7
37
67

Thanks @Ankur. I understood now how SUM() function will execute. Once again thanks to you .... – MilanPanchal Jan 08 '13 at 13:15
Just ran into this exact sort of thing - it looked like it was doing what I wanted it to (returning the number of times an action was completed in a month using `SUM(confirmed = 1)` with a `GROUP BY`) but I wasn't sure if that's what it was *supposed* to be doing - now I know :) – CD001 Feb 13 '13 at 12:01

mysql sum function issue

1 Answers1