CODEWITHSUNDEEP
sqlsql-serversql-server-2008aggregateaggregate-functions
esafresa
esafresa

Reputation: 600

SQL Server : when aggregating, select a single row's value for a specific column

There are three columns in my table, Contract_number, Revenue and Cust_name. I'd like to SUM(Revenue) while grouping by the Contract_number to get the total revenue for that contract. Next to this, I want to display Cust_name to show which customer is under that contract.

The problem is there are multiple Cust_name values under any given contract. The Cust_name values underneath a contract are all extremely similar and I don't care which one of them is used, I just want to display one of them.

If I use something like MIN(Cust_name), I lose data, and if I do GROUP BY Cust_name, then the values are split into multiple rows.

Is there a way to add a single Cust_name value to each result while keep the Contract_number and Revenue aggregate functions the same?

Upvotes: 0

Views: 534

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Use MIN(Cust_Name) or MAX(Cust_Name) together with SUM(Revenue) then GROUP BY Contract_number

SELECT Contract_number,
       SUM(Revenue),
       MIN(Cust_Name) -- or MAX(Cust_Name)
  FROM table
 GROUP BY Contract_number

Upvotes: 1

Related Questions