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?

[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:
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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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

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