totals and percentages

[Destramic]

hey guys i'm working on a query but i'm getting a null on a column which i'm not sort of understanding,,

SELECT
@total := count(user_id),
@positive := sum(case when positive = 1 then 1 else 0 end),
@neutral := sum(case when neutral = 1 then 1 else 0 end),
@negative := sum(case when negative = 1 then 1 else 0 end),
(((@positive + @neutral) - @negative) + @total * 100) AS `sellers_feedback_percentage`
FROM users_feedback WHERE user_id = '1' AND seller = '1

@positive, @neutral and @negative all have the correct value but when trying to make a percentage out of the amounts the sellers_feedback_percentage returns null...only way i can think of doing it is for me to do a subquery for each @positive, @neutral and @negative

 

whats it the best way to return the percentage please?

 

thank you

 

[dalecosp]

Could you show your table schema?

[Destramic]

users_feedback

----------------------------
  `user_feedback_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
 `question` varchar(90) NOT NULL DEFAULT '0',
  `seller` int(1) NOT NULL DEFAULT '0',
  `buyer` int(1) NOT NULL DEFAULT '0',
  `positive` int(1) NOT NULL DEFAULT '0',
 `neutral` int(1) NOT NULL DEFAULT '0',
  `negative` int(1) NOT NULL DEFAULT '0'

-----------------------------------------------------------------

 

 

[Barand]

What's with all the @variables?

SELECT item_id
    , SUM(positive) as totpos
    , SUM(neutral) as totneu
    , SUM(negative) as totneg
    , COUNT(*) as tot
    , SUM(positive + neutral - negative)/COUNT(*)*100 as pcent
FROM user_feedback
GROUP BY item_id;

[Destramic]

 

What's with all the @variables

 

good point =/...not one needed after your help

SELECT 
CONCAT(FORMAT(((SUM(positive + neutral) - negative) / count(*) * 100), 2), '%') AS `seller_feedback_percentage` FROM users_feedback WHERE user_id = '1' AND seller = '1' 

worked like a charm...although i was getting the wrong percentage so i had to put brackets in to get the correct reading...thanks barand

[Barand]

I think you need to recheck your data and results

 

DATA

mysql> SELECT * FROM test.user_feedback WHERE user_id=1;
+------------------+---------+---------+----------+---------+----------+
| user_feedback_id | user_id | item_id | positive | neutral | negative |
+------------------+---------+---------+----------+---------+----------+
|                1 |       1 |       1 |        1 |       0 |        0 |
|                2 |       1 |       2 |        0 |       0 |        1 |
|                3 |       1 |       3 |        1 |       0 |        0 |
+------------------+---------+---------+----------+---------+----------+

My Query

mysql> SELECT user_id
    ->     , SUM(positive) as totpos
    ->     , SUM(neutral) as totneu
    ->     , SUM(negative) as totneg
    ->     , SUM(positive + neutral - negative) as result
    ->     , COUNT(*) as tot
    ->     , SUM(positive + neutral - negative)/COUNT(*)*100 as pcent
    -> FROM user_feedback
    -> WHERE user_id=1
    -> GROUP BY user_id;
+---------+--------+--------+--------+--------+-----+---------+
| user_id | totpos | totneu | totneg | result | tot | pcent   |
+---------+--------+--------+--------+--------+-----+---------+
|       1 |      2 |      0 |      1 |      1 |   3 | 33.3333 |
+---------+--------+--------+--------+--------+-----+---------+

Your Query on the same data

mysql> SELECT
    -> CONCAT(FORMAT(((SUM(positive + neutral) - negative) / count(*) * 100), 2), '%')
    ->     AS `seller_feedback_percentage`
    -> FROM user_feedback
    -> WHERE user_id = '1';
+----------------------------+
| seller_feedback_percentage |
+----------------------------+
| 66.67%                     |
+----------------------------+