MySQL Percentage of 2 columns not working =NULL

Question

I have the following statement in MYSQL:

SELECT Site, Areateam, 
SUM( IF( year = '15-16', 1, 0 ) ) "Y2", 
SUM( IF( year = '14-15', 1, 0 ) ) "Y1",
SUM('Y2') / SUM('Y1')* 100 AS Diff
FROM CD2015_EmailIncidents
WHERE Areateam = 'Greater Manchester'
GROUP BY Site

which returns the following results:

**Site           |Areateam             |Y2  |Y1  |Diff**

Acute Trust      |Greater Manchester   |0   |1   |NULL

Care Home        |Greater Manchester   |3   |22  |NULL

CD Store Room    |Greater Manchester   |7   |4   |NULL

College Greater  |Greater Manchester   |0   |1   |NULL

I cant seem to the correct syntax to show the percentage difference between Y2 and Y1 as it keeps displaying NULL

Many thanks Max

score 0 · Answer 1 · answered Aug 18 '15 at 15:26

0

You can't use calculated column names in the select clause. And first multiplicate with 100 to avoid integer limitations

SUM(year = '15-16') * 100 / SUM(year = '14-15')  AS Diff

answered Aug 18 '15 at 15:26

juergen d

201,996
37
293
362

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

That is because "Y1" and "Y2" doesnt exist on that context.

SELECT Site, Areateam, 
    SUM( IF( year = '15-16', 1, 0 ) ) "Y2", 
    SUM( IF( year = '14-15', 1, 0 ) ) "Y1",
    SUM( IF( year = '15-16', 1, 0 ) ) * 100 / 
    SUM( IF( year = '14-15', 1, 0 ) AS Diff
FROM CD2015_EmailIncidents
WHERE Areateam = 'Greater Manchester'
GROUP BY Site

Check here for rounding ROUND OR TRUNC

edited May 23 '17 at 11:51

Community

1
1

answered Aug 18 '15 at 15:26

Juan Carlos Oropeza

47,252
12
78
118

Wow, many thanks for your super quick reply!, I understand what you are saying your statement works!..thank you! however how can I control the NULL values and set decimal point to 2 places? Site Areateam Y2 Y1 Diff Acute Trust Greater Manchester 0 1 0.0000 Care Home Greater Manchester 3 22 13.6364 CD Store Room Greater Manchester 7 4 175.0000 College Greater Manchester 0 1 0.0000 Dental Practice Greater Manchester 1 0 NULL Drug and Alcohol Service Greater Manchester 0 1 0.0000 GP Practice Greater Manchester 8 21 38.0952 – Max Thorley Aug 18 '15 at 15:33
Dont paste this kind of data in comment, Just update the question instead. – Juan Carlos Oropeza Aug 18 '15 at 15:38
I include a link for the example for round or trunc. Take note the select answer only show TRUNC. You should check the one explaining both – Juan Carlos Oropeza Aug 18 '15 at 15:42

score 0 · Accepted Answer · answered Aug 18 '15 at 15:34

0

Your problem is that you are doing arithmetic on string values:

SUM('Y2') / SUM('Y1')* 100 AS Diff

MySQL does silent conversion, so it converts the values to numbers. There are no leading digits, so they are converted to 0.

MySQL then returns NULL for 0/0.

Juergen has the right solution for this. Your overall query can be expressed as:

SELECT Site, Areateam, 
       SUM(year = '15-16') as Y2, 
       SUM(year = '14-15') as Y1,
       100 * SUM(year = '15-16') / SUM(year = '14-15') AS Diff
FROM CD2015_EmailIncidents
WHERE Areateam = 'Greater Manchester'
GROUP BY Site;

answered Aug 18 '15 at 15:34

Gordon Linoff

1,242,037
58
646
786

Its it possible to adjust the 'diff' decimal point? so it shows 0% or 12% etc...? – Max Thorley Aug 18 '15 at 15:42
You can use `format()` or cast it to a decimal or integer. You'll have to add the `%` using `concat()`. – Gordon Linoff Aug 18 '15 at 22:33

MySQL Percentage of 2 columns not working =NULL

3 Answers3