subquery with aggregate expression

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

SQL Server 2000 Forums

SQL Server Development (2000)

subquery with aggregate expression

Author

Topic

Freshies4eva
Starting Member

7 Posts

Posted - 2005-09-30 : 15:48:22

Hi,
I'm trying to write a query that will present me with a list of top sales locations and dates like so:

Date Loc Total
9/28 100 4
9/27 103 7
9/26 102 6
9/25 100 8

my table is
(item_id int, location int, sell_date)

1. I can get a count of sales by day and location doing a simple count and group

select sell_date, location, cnt = count(*) from sales
group by sell_date, location
order by sell_date desc

2. I can get the top location and date for a specific date using a set of subqueries

select q1.sellDate, q1.loc, q2.cnt from

(select sellDate = sell_date, location as loc, count(*) as cnt from sales
where sell_date = '9/25/2005'
group by sell_date, location) as q1
join
(select location as loc, count(*) as cnt from sales
where sell_date = '9/25/2005'
group by location) as q2 on q1.loc = q2.loc

where q1.cnt =
(select max(cnt) from
(select location as loc, count(*) as cnt from sales
where sell_date = '9/25/2005'
group by location
) as q3
)

BUT, I can't figure out how select all dates and end up with a descending list by date of the top sales location.

Any ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-30 : 15:58:14

Not too sure what you want

select t.loc, t.sell_date, count(*)
from sales t
join (select top 1 loc, count(*) from sales t2 group by loc order by count(*)) t2
on t2.loc = t.loc
group by t.loc, t.sell_date
order by t.sell_date desc

That's descending by date for the top sales location so matches the description but not the queries you have given.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Freshies4eva
Starting Member

7 Posts

Posted - 2005-10-04 : 12:49:06

Thanks for your response. Didn't think about TOP, that is a good idea. However, this doesn't accomplish one of the goals which is to select the top location by day over a period of days. I played around but had no luck breaking it out by day over a period of days. Given this...

location sell_date Unit Sales
----------- ------------------------------------
102 2005-10-03 00:00:00 3
100 2005-10-03 00:00:00 1
101 2005-10-03 00:00:00 1
101 2005-10-02 00:00:00 3
102 2005-10-02 00:00:00 1
100 2005-10-02 00:00:00 1
100 2005-10-01 00:00:00 3
101 2005-10-01 00:00:00 1
102 2005-10-01 00:00:00 1

I want to end up with...
sell_date location num sales
10/3/2005 102 3
10/2/2005 101 3
10/1/2005 100 3

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-05 : 01:30:32

Try this

Select sell_date, location, [unit sales] as [num sales] from yourTable T
Where [unit sales]=(Select Top 1 [unit sales] from yourTable where sell_date=T. sell_date
and location=T.location)

Madhivanan

Failing to plan is Planning to fail

Freshies4eva
Starting Member

7 Posts

Posted - 2005-10-05 : 11:03:53

That query returns the original (aggregated) results. Did I miss something? Below is a sample of the original listing: the id of the item sold, where it was sold, and on what date. I'd love to go from this to a result showing the top sales location by day.


item_id location sell_date
1	100	2005-10-01 00:00:00
2	100	2005-10-01 00:00:00
3	100	2005-10-01 00:00:00
4	101	2005-10-01 00:00:00
5	102	2005-10-01 00:00:00
6	100	2005-10-02 00:00:00
7	101	2005-10-02 00:00:00
8	101	2005-10-02 00:00:00
9	101	2005-10-02 00:00:00
10	102	2005-10-02 00:00:00
11	100	2005-10-03 00:00:00
12	101	2005-10-03 00:00:00
13	102	2005-10-03 00:00:00
14	102	2005-10-03 00:00:00
15	102	2005-10-03 00:00:00

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 01:05:29

Is this?

Select item_id, location, sell_date from yourTable T
Where sell_date =(Select Top 1 sell_date from yourTable where location=T.location)

Otherwise post the result you want

Madhivanan

Failing to plan is Planning to fail

Freshies4eva
Starting Member

7 Posts

Posted - 2005-10-11 : 15:20:13

Thanks for your suggestion. I'm going to include a sample of the table and the desired output.

I'm trying to summarize this table (sales), showing item id, location where it was sold, and date sold


item_id     location    sell_date                                              
----------- ----------- ------------------- 
1           100         2005-10-01 00:00:00
2           100         2005-10-01 00:00:00
3           100         2005-10-01 00:00:00
4           101         2005-10-01 00:00:00
5           102         2005-10-01 00:00:00
6           100         2005-10-02 00:00:00
7           101         2005-10-02 00:00:00
8           101         2005-10-02 00:00:00
9           101         2005-10-02 00:00:00
10          102         2005-10-02 00:00:00
11          100         2005-10-03 00:00:00
12          101         2005-10-03 00:00:00
13          102         2005-10-03 00:00:00
14          102         2005-10-03 00:00:00
15          102         2005-10-03 00:00:00

into this query view (top sales leader by day)


sell_date  location    num_sales   
---------- ----------- ----------- 
10/03/05   102         3
10/02/05   101         3
10/01/05   100         3

which shows the top sales location, the number of items it sold that day, and the date. I would like to show more than the top location, but trying to keep it simple right now.

Thank you for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 03:25:29

Not sure whether this will work for all data


Select * from 
	(
	Select Sell_date, Location,count(Location) as num_sales from yourTable group by Sell_date, Location 
	 ) T
where num_sales=(select Top 1 count(*) from yourTable group by sell_date,Location order by count(*) desc)

Madhivanan

Failing to plan is Planning to fail

Freshies4eva
Starting Member

7 Posts

Posted - 2005-10-13 : 14:36:36

We're getting closer! Excuse me, I should say you are getting closer :)

This works, but only because the table data I provided was such that on any one of the three days the leading location had 3 total sales. I added one more for location 1 (note the last row - 16) and that breaks the query.

Here is the new table...


1	100	2005-10-01 00:00:00
2	100	2005-10-01 00:00:00
3	100	2005-10-01 00:00:00
4	101	2005-10-01 00:00:00
5	102	2005-10-01 00:00:00
6	100	2005-10-02 00:00:00
7	101	2005-10-02 00:00:00
8	101	2005-10-02 00:00:00
9	101	2005-10-02 00:00:00
10	102	2005-10-02 00:00:00
11	100	2005-10-03 00:00:00
12	101	2005-10-03 00:00:00
13	102	2005-10-03 00:00:00
14	102	2005-10-03 00:00:00
15	102	2005-10-03 00:00:00
16	100	2005-10-01 00:00:00

Here is your query (other than formatting I only changed it so it outputs results by date desc).


select * from 
	( select Sell_date, 
			 Location,
			 count(Location) as num_sales
		from sales
		group by Sell_date, Location
	 ) T
where num_sales = 
	( select Top 1 count(*)
		from sales
		group by sell_date,Location 
		order by count(*) desc
	)
order by Sell_date desc

With the new table, you will see that it only returns one row, for Oct 1, because that is where num_sales matches the derived table. This has been bugging me for a couple weeks, getting close. I really appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources