Acerace.py Acerace.py - 1 year ago 115
MySQL Question

Application of a Formula on MySql Table Column after Counting the Occurrences of Items inside the Column.

I have a table which looks like:

brand satisfaction
A offered
N good
L bad
A good
N good
L good
A bad
N bad
L offered
A offered
N bad
L bad
N bad
L bad


By applying :

SELECT brand,satisfaction, COUNT(satisfaction) as Number
FROM
table
WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
GROUP BY brand, satisfaction


gave me a new table (table2) which looks like:

brand satisfaction Number
A offered 2
A good 1
A bad 1
N offered 0
N good 2
N bad 3
L offered 1
L good 1
L bad 3


I want to apply a formula
((good-bad)/good+bad+offered)
grouped by brand to get result table (table3) like this:

brand afterformula
A 0
N -0.2
L -0.5


What have I tried so far?. I have been able to solve the problem but only by first saving the table2 in database and applying the following query:

SELECT brand, (MAX(CASE WHEN satisfaction = 'good' THEN Number ELSE 0 end )-MAX(CASE WHEN satisfaction = 'bad' THEN Number ELSE 0 end ))
/ (MAX(CASE WHEN satisfaction = 'bad' THEN Number ELSE 0 end )+ MAX(CASE WHEN satisfaction = 'good' THEN Number ELSE 0 end )+ MAX(CASE WHEN satisfaction = 'offered' THEN Number ELSE 0 end )) AS
'afterformula'
FROM table2
group by brand


The problem is that I need to apply the formula to the first table itself for the automation job that I need it for and the original table is huge with about 5 million rows. Could anyone please help me or give me tips, how could I achieve the result. Thank you for your time.

Answer Source

Use conditional aggregation to calculate each value in a single row:

SELECT brand,
       SUM(satisfaction = 'good') as good,
       SUM(satisfaction = 'bad') as  bad,
       SUM(satisfaction = 'offered') as offered
FROM table
WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
GROUP BY brand;

Then you can use a subquery (or complicated formula in the above):

SELECT b.*,
       (good - bad) / (good + bad + offered) as formula
FROM (SELECT brand,
             SUM(satisfaction = 'good') as good,
             SUM(satisfaction = 'bad') as  bad,
             SUM(satisfaction = 'offered') as offered
      FROM table
      WHERE satisfaction LIKE 'good' OR satisfaction LIKE 'bad' OR satisfaction LIKE 'offered'
      GROUP BY brand
     ) b;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download