We help IT Professionals succeed at work.

Help to sum mysql query data

ltpitt
ltpitt asked
on
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!
Comment
Watch Question

Top Expert 2015

Commented:
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...

Author

Commented:
I get wrong numbers...

The day total is 8207

and your query results:

0      JONATHAN
9783.600079834461      SARAH
6631.300042629242      WERONIKA
Top Expert 2015

Commented:
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?

Author

Commented:
I need the total per waiter, just that...
Top Expert 2015

Commented:
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...

Author

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)
Top Expert 2015

Commented:
Can you post individual records in excel file because i need to see what's going in here as the query remains this only..
Top Expert 2015

Commented:
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..

Author

Commented:
I can let you access phpmyadmin directly!

see here:

http://davidenastri.it/phpmyadmin

enter with:
saurabh

pass:
saurabh

Author

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
Commented:
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
Top Expert 2015
Commented:
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...

Author

Commented:
Both worked for me!

Thanks!