Show the Lowest and highest sale along with saleda

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Show the Lowest and highest sale along with saleda

Author

Topic

ljacob01
Starting Member

4 Posts

Posted - 2011-12-05 : 22:11:44

I'm sorry if this has been posted before but I could not find help anywhere. But I'd really appreciate it if you guys could help me out here.
I need to run a query which shows the store no., it's highest sale, lowest sale, and the dates on which these sales occurred. I've gotten this far:
SELECT MAX(AMT) AS HIGHEST_SALE, MIN(AMT) AS LOWEST_SALE, STORE
FROM BASKET
GROUP BY STORE
ORDER BY STORE

The problem is I don't know how to post the saledate of the lowest sale and highest sale for each store next to them. So I would really appreciate if you guys could help me out. Thanks!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-05 : 22:41:11

What is the version of SQL Server you are using ?

KH
[spoiler]Time is always against us[/spoiler]

ljacob01
Starting Member

4 Posts

Posted - 2011-12-06 : 09:39:43

it's microsoft sql server management studio 2008

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 18:53:55

[code]
select h.STORE,
h.AMT as HIGHEST_SALE, h.SALEDATE as HIGHEST_SALEDATE,
l.AMT as LOWEST_SALE, l.SALEDATE as LOWEST_SALEDATE
from (
select STORE, SALEDATE, AMT, RN = row_number() over (partition by STORE order by AMT desc)
from BASKET
) h
inner join
(
select STORE, SALEDATE, AMT, RN = row_number() over (partition by STORE order by AMT)
from BASKET
) l ON h.STORE = l.STORE
where h.RN = 1
and l.RN = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ljacob01
Starting Member

4 Posts

Posted - 2011-12-06 : 19:16:06

thanks a lot. but can you help me out with few questions? what are these h and l for? i've been taught to use max and min only. also is there a way to make this query through create view command??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 20:26:42

h & l are alias name given to the derived table.

yes. You can use this query for a view

KH
[spoiler]Time is always against us[/spoiler]

ljacob01
Starting Member

4 Posts

Posted - 2011-12-06 : 21:13:38

Nevermind I was able to realize that those were derived tables. Thanks a lot for your help!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources