Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
SQL only use record with higher value
Hello,
I am using SQL to create a query which is basically a big case when statement involving a lot of codes. However, there can be two records for some of the people in the database.
For Example:
Name |Revenue|
John | $100|
Mike | $200|
Bob | $50|
Bob | $300|
In the above example "Bob" appears twice. In these cases I only want to use the record where the Revenue is highest. So in this case I only want to use the record where the Revenue is $300.
Most of our records only have 1 name so most of the time there is just "John" or "Mike"
Is there a way to do this in SQL?
Thanks for any help,
Mike
I am using SQL to create a query which is basically a big case when statement involving a lot of codes. However, there can be two records for some of the people in the database.
For Example:
Name |Revenue|
John | $100|
Mike | $200|
Bob | $50|
Bob | $300|
In the above example "Bob" appears twice. In these cases I only want to use the record where the Revenue is highest. So in this case I only want to use the record where the Revenue is $300.
Most of our records only have 1 name so most of the time there is just "John" or "Mike"
Is there a way to do this in SQL?
Thanks for any help,
Mike
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
That would be a pretty simple MAX statement. For more info on how this works check out SQL Server GROUP BY .
SELECT Name, MAX(Revenue) as Revenue
FROM YourTable
GROUP BY Name
Thank you. I should have been more specific in my question. There are other fields in the table like location, zip code, groupid, and count (count is always equal to 1).
If I wanted to do an aggregate function to get the sum of the count is there any way to do that?
Thanks again,
Mike
If I wanted to do an aggregate function to get the sum of the count is there any way to do that?
Thanks again,
Mike
Premium Content
You need an Expert Office subscription to comment.Start Free Trial