Jump to content

totals and percentages


Destramic
Go to solution Solved by Barand,

Recommended Posts

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

 

Link to comment
Share on other sites

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'

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

 

 

Link to comment
Share on other sites

 

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 :happy-04:

Link to comment
Share on other sites

  • 2 weeks later...

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%                     |
+----------------------------+
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.