asked on

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!

Saurabh Singh Teotia

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

ltpitt

ASKER

I get wrong numbers...

The day total is 8207

and your query results:

0      JONATHAN
9783.600079834461      SARAH
6631.300042629242      WERONIKA

Saurabh Singh Teotia

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?

ltpitt

ASKER

I need the total per waiter, just that...

Saurabh Singh Teotia

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

ltpitt

ASKER

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)

Saurabh Singh Teotia

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

Saurabh Singh Teotia

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

ltpitt

ASKER

I can let you access phpmyadmin directly!

see here:

http://davidenastri.it/phpmyadmin

enter with:
saurabh

pass:
saurabh

ltpitt

ASKER

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

ASKER CERTIFIED SOLUTION

Tj a

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ltpitt

ASKER

Both worked for me!

Thanks!