MySQL variable only get last value

Question

i need help with query i have table with this format

  uniqueid|createdDate|ask1|ask2|ask3|ask4|ask5 
  --------------------------------------------
  10001   |2020-09-30 |Yes |Yes |Yes |Yes |Yes 
  10002   |2020-09-30 |Yes |No  |Yes |Yes |Yes

i want result query like this

  uniqueid|createdDate|Yes|No|percent
  --------------------------------
  10001   |2020-09-30 |5  |0 |100%
  10002   |2020-09-30 |4  |1 |95%

this is my query

SELECT 
  uniqueid,
  createdDate,
  @yes := SUM(CASE WHEN ask1 = 'Yes' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask2 = 'Yes' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask3 = 'Yes' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask4 = 'Yes' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask5 = 'Yes' THEN 1 ELSE 0 END) as 'Yes',
 SUM(CASE WHEN ask1 = 'No' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask2 = 'No' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask3 = 'No' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask4 = 'No' THEN 1 ELSE 0 END)+SUM(CASE WHEN ask5 = 'No' THEN 1 ELSE 0 END) as 'No',
 FLOOR(@yes/5)*100 as 'percent'
FROM table
GROUP BY uniqueid

why @yes only get last value from last row data. i need help to fix this.

1) UDV won't be processed correctly in grouping query. 2) Explain the algorithm which produces such output from shown source data (especially 95% value). — Akina, Sep 30 '20 at 08:17
This is a very bad database design. You should put your answers in an own table related to the first table. Then you cannot even calc the percentages very easy you are also free to use as many answers you like without changing the database every time — Thallius, Sep 30 '20 at 08:18
https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field — Dark Knight, Sep 30 '20 at 08:20

score 2 · Accepted Answer · answered Sep 30 '20 at 08:17

The order of evaluation of expressions in the select clause is undefined, so there is no guarantee that you code will consistently do what you want.

I would recommend repeating the expression, or using a subquery rataher than relying on variables:

select t.*, 5 - cnt_yes cnt_no, floor(cnt_yes * 100 / 5) percent
from (
    select t.*,
        (ask1 = 'Yes') + (ask2 = 'Yes') + (ask3 = 'Yes') + (ask4 = 'Yes') + (ask5 = 'Yes') cnt_yes
    from mytable t
) t

Note that, for your sample data, there is no need for aggregation to get the result you want (you have one row per uniqueid to start with).

MySQL variable only get last value

1 Answers1