CODEWITHSUNDEEP
sqlsql-servert-sql
user710502
user710502

Reputation: 11471

Querying table with group by and sum

I have the following table called Orders

  Order  | Date      |    Total
------------------------------------
 34564   | 03/05/2015|  15.00
 77456   | 01/01/2001|   3.00
 25252   | 02/02/2008|   4.00
 34564   | 03/04/2015|   7.00

I am trying to select the distinct order sum the total and group by order #, the problem is that it shows two records for 34564 because they are different dates.. How can I sum if they are repeated orders and pick only the max(date) - But sill sum the total of the two instances?

I.E result

  Order  | Date      |    Total
------------------------------------
 34564   | 03/05/2015|  22.00
 77456   | 01/01/2001|   3.00
 25252   | 02/02/2008|   4.00

Tried:

SELECT DISTINCT Order, Date, SUM(Total)
FROM Orders
GROUP BY Order, Date

Of couse the above won't work as you can see but i am not sure how to achieve what i intend.

Upvotes: 2

Views: 68

Answers (5)

Mead Umandal
Mead Umandal

Reputation: 373

Just add MAX(Date) to your SELECT clause. Try this : 

SELECT DISTINCT Order, MAX(Date), SUM(Total)
FROM Orders
GROUP BY Order, Date

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

SELECT [order], MAX(date) AS date, SUM(total) AS total 
FROM Orders o
GROUP BY [order]

Upvotes: 4

Syakur Rahman
Syakur Rahman

Reputation: 2102

Simplest query should be:

SELECT MAX(Order), MAX(Date), SUM(Total)
FROM Orders

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You can use the MAX aggregate function to choose the latest Date to appear from each Order group:

SELECT Order, MAX(Date) AS Date, SUM(Total) AS Total
FROM Orders
GROUP BY Order

Upvotes: 3

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use SUM and MAX together:

SELECT
    [Order],
    [Date] = MAX([Date]),
    Total = SUM(Total)
FROM tbl
GROUP BY [Order]

A word of advice, please refrain from using reserved words like Order and Date for your columns and table names.

Upvotes: 1

Related Questions