ltpitt
asked on
How can I sum a column in my Mysql query based on other column?
I have this query:
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!
SELECT distinct(number), user_name, doc_total FROM `docs` inner join getuti on docs.user = getuti.user_number WHERE date = '2015-02-24'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!
Saurabh Singh Teotia
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...
ASKER
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.
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.
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...
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...
ASKER
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.
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.
Can i Safely assume the guy serving the table first should get the money for it..??
Saurabh...
Saurabh...
ASKER
You can assume 100% that if "SARAH" has a ticket number (example N.72) it's only handled by her (just examples)
Use this query...
Saurabh...
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_nameSaurabh...
ASKER
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...
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...
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...
ASKER
If you check better excel the rows for each ticket are doubled (or more) that is the why of my distinct
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
So you see i don't see a difference so i'm not sure what you are referring to...
Enclosed is the screenshot for your reference...
So you see i don't see a difference so i'm not sure what you are referring to...
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.
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.
Walter,
I checked the sample data and that's not the case is..Not sure if i'm missing something..
Saurabh..
I checked the sample data and that's not the case is..Not sure if i'm missing something..
Saurabh..
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thanks for the incredible help and for pointing out a problem that needs to be handled ( Saurabh Singh Teotia)...
You're both great!
You're both great!