Menu
I currently have a table that looks something like this:
+------+-------+------------+------------+ | id | rate | first_name | last_name | +------+-------+------------+------------+ What I need to do is get the SUM of the rate column, but only once for each name. For example, I have three rows of name John Doe, each with rate 8. I need the SUM of those rows to be 8, not 24, so it counts the rate once for each group of names.
SUM(DISTINCT last_name, first_name) would not work, of course, because I'm trying to sum the rate column, not the names. I know when counting individual records, I can use COUNT(DISTINCT last_name, first_name), and that is the type of behavior I am trying to get from SUM.
How can I get just SUM one rate for each name?
Thanks in advance!
408
In SQL multiple fields may also be added with DISTINCT clause. DISTINCT will eliminate those rows where all the selected fields are identical.
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
By using the WHERE clause with a DISTINCT clause in MySQL queries, we are putting a condition on the basis of which MySQL returns the unique rows of the result set.
select sum (rate) from yourTable group by first_name, last_name Edit
If you want to get all sum of those little "sums", you will get a sum of all table..
Select sum(rate) from YourTable but, if for some reason are differents (if you use a where, for example) and you need a sum for that select above, just do.
select sum(SumGrouped) from ( select sum (rate) as 'SumGrouped' from yourTable group by first_name, last_name) T1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
