How can I sum a column in my Mysql query based on other column?

I have this query:

SELECT distinct(number), user_name, doc_total FROM `docs` inner join getuti on docs.user = getuti.user_number WHERE date = '2015-02-24'

Open in new window


and I get this result:

number      user_name      doc_total (this is the income amount)
70      SARAH      8.25
71      SARAH      2.7
72      SARAH      19.65
73      SARAH      13
74      SARAH      21.75
and so on...

How can I sum all the doc_total values dividing those for each user_name?

Correct server response should be a few rows containing (colum names are example):

waiter_name  waiter_total_per_day

If more is needed I can provide access to my learning mysql server:

http://davidenastri.it/phpmyadmin

user:
saurabh

passwd:
saurabh

Thanks for your kind help!
LVL 1
ltpittAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
What is this number for..?? What does it shows?? and do you want this number or you can skip it because what i understand this number is unique so you can't do a group by using this number because it won't get clubbed as one...
0
ltpittAuthor Commented:
The number is the ticket number.

When a waiter attends to a table and I get a coca cola and you get a sandwich the ticket gets two numbers (example: 2 and 2) but the total (doc_total) is repeated in every row.

for this reason if I skip using distinct on the number row I sum wrong values.
0
Saurabh Singh TeotiaCommented:
So even if you apply distinct on number and if that table number has 2 entries by the way of 2 waiters it will pick both the entries and assuming the doc_total is shown in both the entries i mean consolidated amount so it will show doc_total as well consolidated for both the waiters..

Now in this case do you also store the date and time of the number entry and if you do then which date and time you want to take..also on the link that you gave to me to write sql i'm not sure where to write and test it..so need your help their...
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

ltpittAuthor Commented:
Sure!

No waiter can interfere into another waiter's ticket.

If you enter with your credentials here:

http://davidenastri.it/phpmyadmin

you get into an interface that will allow you to select a database on the left and you'll choose "cosmonet"

done that you'll see all database tables and you'll have a "sql" button where you can enter your queries and see the results.
0
Saurabh Singh TeotiaCommented:
Can i Safely assume the guy serving the table first should get the money for it..??

Saurabh...
0
ltpittAuthor Commented:
You can assume 100% that if "SARAH" has a ticket number (example N.72) it's only handled by her (just examples)
0
Saurabh Singh TeotiaCommented:
Use this query...

select sum(total) as tot,tb2.user_name as waiter_name 
from 
(select number,user,sum(doc_total) as total,date
 from docs
group by number,user,date)  tb1
left join getuti tb2 on tb1.user = tb2.user_number 
where DATE(tb1.date) = '2015-02-24' 
group by tb2.user_name

Open in new window


Saurabh...
0
ltpittAuthor Commented:
I get the wrong total...

If you see carefully the same ticket number gets the total amount for each row.

That is why I used a distinct on the number field...
0
Saurabh Singh TeotiaCommented:
I compared in excel and it's matching with your total for the day so when you say wrong total..can please post screenshots of what you are seeing which is wrong...
0
ltpittAuthor Commented:
If you check better excel the rows for each ticket are doubled (or more) that is the why of my distinct
0
Walter RitzelSenior Software EngineerCommented:
I think the query is almost right, but reading all the thread, one thing seems to be wrong: If in the internal query you group like that, all the values on each row will be summed up, and that would double, triple the value, as it was stated that value is already consolidated. So, I would say that you can change that to this:
select sum(total) as tot,tb2.user_name as waiter_name 
from 
(select distinct number, user, doc_total as total, date
 from docs)  tb1
left join getuti tb2 on tb1.user = tb2.user_number 
where DATE(tb1.date) = '2015-02-24' 
group by tb2.user_name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
I'm not sure i follow you here because here is the two screenshots for your reference where if you see the total vs waiter it matches with each other...

Enclosed is the screenshot for your reference...

Total
waiter
So you see i don't see a difference so i'm not sure what you are referring to...
0
Walter RitzelSenior Software EngineerCommented:
Saurabh,
the problem mentioned by the author is that, for example: if a Number (The Ticket ID) appears in 2 rows for the same date and user, the doc_total field will contain on both rows the same value, which represents the ticket total. So, if you simply sum up the values, you are doubling the value.
The math on your queries are not taking that into account, you are simply summing up the rows with no regards of the business rule mentioned by the author.
0
Saurabh Singh TeotiaCommented:
Walter,

I checked the sample data and that's not the case is..Not sure if i'm missing something..

Saurabh..
0
Walter RitzelSenior Software EngineerCommented:
Please check with this query:

SELECT *
FROM docs
WHERE number =8

It is pretty clear that were 3 rows with the same doc_total, but with different products being sold.
0
Saurabh Singh TeotiaCommented:
Walter,

Thanks for pointing out...Yeah i think distinct will take care of the same, However my only fear is if the same user served the same table on a same date and co-incidentally if the amount of this transaction is same..It's going to pick up only 1 transaction which in reality it's two because it can happen that the same waiter servers on the same table again..As in real life this scenario is very much possible if you go on a  restaurant and i'm assuming this that data only...

I prefer using total column which is in the end as it shows the correct picture.. However the only drawback with that column that it doesn't capture the discount which is given to the customer..otherwise that column is perfect..

Saurabh..
0
Walter RitzelSenior Software EngineerCommented:
Ok, that is a possibility.
But then, instead of using doc_total, the author would need to use a formula involving the total of the product on that line minus discounts plus taxes and so on... basically duplicating the code used for doc_total. Then your query would work as a charm.
0
ltpittAuthor Commented:
Thanks for the incredible help and for pointing out a problem that needs to be handled ( Saurabh Singh Teotia)...

You're both great!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.