Destramic Posted August 11, 2015 Share Posted August 11, 2015 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 Quote Link to comment Share on other sites More sharing options...
dalecosp Posted August 11, 2015 Share Posted August 11, 2015 Could you show your table schema? Quote Link to comment Share on other sites More sharing options...
Destramic Posted August 11, 2015 Author Share Posted August 11, 2015 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' ----------------------------------------------------------------- Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted August 11, 2015 Solution Share Posted August 11, 2015 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; Quote Link to comment Share on other sites More sharing options...
Destramic Posted August 12, 2015 Author Share Posted August 12, 2015 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted August 24, 2015 Share Posted August 24, 2015 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% | +----------------------------+ Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.