Help to sum mysql query data

Hello there!

With this query:

select doc_total as total, user_name as waiter_name from docs, getuti where DATE(date) = '2015-02-24' and user = user_number 

Open in new window


I get a long list of results with one column "total" and the other "waiter_name" like this little example:


total      waiter_name
8.25        SARAH
8.25       JOHN
2.7         JOHN
2.7         SARAH
19.65      SARAH

I would like to get each waiter's total...

Can I do it in mysql?

Thanks!
LVL 1
ltpittAsked:
Who is Participating?
 
Tj aCommented:
Hi Itpitt,

Try this,

SELECT DISTINCT(user_name) AS waiter_name, SUM(doc_total) AS total
FROM docs, getuti
WHERE DATE(date) = '2015-02-24' AND user = user_number
GROUP BY waiter_name

Open in new window


Hope it works
0
 
Saurabh Singh TeotiaCommented:
Use this query...

select sum(doc_total) as total, user_name as waiter_name 
from docs, getuti 
where DATE(date) = '2015-02-24' and user = user_number 
group by user_name

Open in new window


Saurabh...
0
 
ltpittAuthor Commented:
I get wrong numbers...

The day total is 8207

and your query results:

0      JONATHAN
9783.600079834461      SARAH
6631.300042629242      WERONIKA
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Saurabh Singh TeotiaCommented:
If you see the waiter total will be right what you are getting individually..Help me understand along with day totals you are also looking for cumulative total for the day as well as in total for all waiters?
0
 
ltpittAuthor Commented:
I need the total per waiter, just that...
0
 
Saurabh Singh TeotiaCommented:
The above report shows total by waiter only...Manually you can compare total which you are getting with each waiter to this query..both answer will be same only...
0
 
ltpittAuthor Commented:
The above report is wrong because if I do:

select SUM(doc_total) as total from docs where DATE(date) = '2015-02-24'

I get:

8207,45€

If I do you query I get:

0      JONATHAN
9783.600079834461      SARAH
6631.300042629242      WERONIKA

which is, of course, impossibile (SARAH earns more than the day total)
0
 
Saurabh Singh TeotiaCommented:
Can you post individual records in excel file because i need to see what's going in here as the query remains this only..
0
 
Saurabh Singh TeotiaCommented:
And run this query and let me know...

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

Open in new window


Also help me understand which tables has date and which tables has username and doc so that i can accordingly tweak this query..
0
 
ltpittAuthor Commented:
I can let you access phpmyadmin directly!

see here:

http://davidenastri.it/phpmyadmin

enter with:
saurabh

pass:
saurabh
0
 
ltpittAuthor Commented:
If I use a simple query (single table) I get no problems at all:

select SUM(doc_total)as total, user from docs where DATE(date) = '2015-02-24' group by user order by total DESC limit 3

Gives me back the correct values (missing correct names):

4891.800039917231      8
3315.650021314621      5
0      1
0
 
Saurabh Singh TeotiaCommented:
Use this query..this will do what you are looking for..

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

Open in new window


Also like takunda posted out and it seems you have multiple users in the same table with same user id..so i will recommend clearing your table as well to ensure it shows correct results as it has lot of duplicate data in it..

Saurabh...
0
 
ltpittAuthor Commented:
Both worked for me!

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.