CODEWITHSUNDEEP
mysqlgroup-by
Kiel
Kiel

Reputation: 493

Query with distinct and group by

enter image description here

How do I get 9,300 only out of the table above? I just need to add 6500 + 1800 + 1000

Here is my current query

SELECT 
SUM(e.amount) / (SELECT count(e2.receipt_no) 
                 FROM entries e2 
                 WHERE e2.receipt_no = e.receipt_no) as total,
e.user_id
FROM
entries e
GROUP BY e.receipt_no

The result is

enter image description here

Now i need to get the total per user_id

Expected output should be

enter image description here

Upvotes: 1

Views: 55

Answers (5)

Passionate Coder
Passionate Coder

Reputation: 7294

You can also try this

SELECT  user_id, SUM(DISTINCT `amount`) FROM `test` group by `user_id`

Step 1: Select distinct amount for each user id

101 -  6500,1800,1000 
189 - 1019.00

Step - 2 

101 = 6500+1800+1000 = 9300.00
189 = 1019.00

This will select distinct amount for each user id and then add selected amount and give you same result.

Upvotes: 0

Govind Samrow
Govind Samrow

Reputation: 10179

First calculate DISTINCT amount group by userid,receipt_no and then sum of there entries group by user_id:

SELECT sum(total),userid from (SELECT sum(DISTINCT amount) as total, 
userid,receipt_no FROM entries GROUP BY userid,receipt_no) as rgrouped 
GROUP BY userid

Upvotes: 1

Jeruson
Jeruson

Reputation: 125

Try this

 SELECT SUM(amount) as total,user_id FROM entries GROUP BY user_id

Upvotes: 1

Shibon
Shibon

Reputation: 1574

Try some thing like this 

SELECT SUM(DISTINCT(amount)) as total, user_id FROM `entries` GROUP BY user_id

Upvotes: 1

bmpickford
bmpickford

Reputation: 90

From my understanding this should give you want you want

SELECT sum(DISTINCT amount) as total, reciept_no FROM entries GROUP BY receipt_no

Upvotes: 1

Related Questions