Take the Min Value

[michaela18]

Hi,

I was wondering if anyone knows how to do this.
I have the follow fields in a table:

Date
G_id
Gz_id
Owner_id
Views
Sales

I want to pull by Owner_id, all the sales by date, but only the Minimum Views by date by G_Id and then GZ_ID

For example:

On 01/01/2011, there are 2 records, both have G_ID=10, GZ_ID= a and GZ_ID=b, same owner ID, views = 1000 for GZ_ID=a and 1002 for GZ_ID=b, sales were 10 and 20 respectively.

I want to pull just the Views from GZ_id = a since it has the lesser views for that day, but I want to pull both the sales for that day.

 

[Qik3Coder]

What do you have so far?


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[markros]

It will be easier to answer if you post some data and desired output. It looks like you may need joins with other tables that you didn't mention.

PluralSight Learning Library

 

[Olaf Doschke]

You may want:

Select owner_id, g_id, min(views) as MinViews, sum(sales) as totalsales From yourtable Group By owner_id, g_id

But as markros says, that's not for sure, also you don't specify what else you want in the results but the minimum view and total sales.

Bye, Olaf.

 

[starsky51]

I think I understand what you are after. You want to pull the all of the sales records but only show the minimum view value alongside each record.

The PARTITION BY clause let's you do this.

eg.
DECLARE @test TABLE(ID int, sales int, views int)
INSERT INTO @test VALUES (1, 1, 10)
INSERT INTO @test VALUES (1, 2, 20)
INSERT INTO @test VALUES (1, 3, 30)
INSERT INTO @test VALUES (2, 4, 100)
INSERT INTO @test VALUES (2, 5, 50)

SELECT ID, sales, MIN(views) OVER (PARTITION BY ID) FROM @test