Acerace.py - 1 year ago 151
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
A   good
N   good
L   good
L   offered
A   offered
``````

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
N   offered        0
N   good           2
L   offered        1
L   good           1
``````

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.

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

``````SELECT brand,
SUM(satisfaction = 'good') as good,
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.*,