Multiple aggregate functions

Question

In a SQL Server table with Item, Supplier, Value and Date columns where are records of all item purchases, I need records, by item, with minimum value of the last 180 days only.

A sample table with only one Item:

Item   | Supplier | Value | Date          
---------------------------------------
123    |   28     |  115  | 2013-09-25 
123    |   25     |  125  | 2013-11-30 
123    |   30     |  120  | 2014-01-15 
123    |   25     |  130  | 2014-04-30

The query result should be:

123    |   30     |  120  | 2014-01-15

It is possible have records that seems to be duplicated because it can be more than one origin document from the same supplier and date with same Item and value. Only need one of this records.

How can I get it?

Tim Schmelter · Accepted Answer · 2014-05-09 08:15:18Z

2

Use a ranking function like Row_Number:

WITH CTE AS
(
  SELECT Item, Supplier,Value, Date,
         RN = ROW_NUMBER() OVER (PARTITION BY Item
                                 ORDER BY Supplier DESC)
  FROM dbo.TableName
  WHERE DATEDIFF(day, Date, GetDate()) <= 180
)
SELECT Item,  Supplier,Value, Date
FROM CTE
WHERE RN = 1

Demo

However, it's not that clear what you want of the duplicates or what a duplicate is at all. I have presumed that the Item determines a duplicate and that the maximum Supplier is taken from each duplicate group.

If you want to use all columns to determine a duplicate your desired result would contain multiple rows because there is no duplicate at all.

answered May 9, 2014 at 8:15

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PJLG Over a year ago

Almost perfect! Thank you @Tim! Only need to change ORDER BY Value, Date DESC to serve all my purposes.

Fabian Bigler · Accepted Answer · 2014-05-09 08:20:50Z

0

Another simple way of achieving this:

SELECT TOP 1 * FROM TableName
WHERE DATEDIFF(day, Date, GetDate()) <= 180
ORDER BY VALUE

Demo

answered May 9, 2014 at 8:20

Fabian Bigler

10.9k6 gold badges52 silver badges74 bronze badges

Comments

Vishwanath Dalvi · Accepted Answer · 2014-05-09 08:21:01Z

0

select item, supplier, min_value
from
(
select item, supplier, min_value, row_number() over(order by min_value) as row_num
from
(
select item, supplier, min(value) as min_value
from sales
where DATEDIFF(DAY, mydate ,GETDATE()) <= 180
group by item, supplier
) dt
) dt1
where dt1.row_num = 1

answered May 9, 2014 at 8:21

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

Collectives™ on Stack Overflow

Multiple aggregate functions

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related