CODEWITHSUNDEEP
sql-server-2008t-sqlgrouping
DtotheG
DtotheG

Reputation: 1307

Grouping with Nulls T-SQL

I have this problem with a SQL query (SQL Server 2008) 

SELECT id, client, SUM(debt), date
FROM Table GROUP BY id, client, date

Returned from the query is

id client debt date
1  jim x  500  05/05/2012
2  jack a 900  06/06/2012
2  jack a 500  null

Is there a way to add in this scenario Jack a's debt (1400) and display the non null date i.e. 06/06/2012.

A person can only have 2 records max and 1 record is always date null so is there a way to do the sum and use the date that is not null?

Thanks

Upvotes: 2

Views: 1206

Answers (1)

Mark Byers
Mark Byers

Reputation: 838166

To group by client you have to remove id and date from your GROUP BY:

SELECT
    MAX(id) AS newest_id,    -- or MIN(id) if you prefer
    client,
    SUM(debt) AS total_debt,
    MAX(date) AS most_recent -- or MIN(date) if you prefer
FROM YourTable
GROUP BY client

Upvotes: 4

Related Questions