Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How to loop through a dataset using t-sql

Posted on 2009-03-30
Medium Priority
453 Views
I have a database that has different prices for different items from different stores.  I've given each item a standard number, and an indicator to tell where the item came from.  What I'd like to do is create a procedure to give me a percent price difference based on the average of all items.  So for the sample data below, I want the average price of all items from Wal-Mart, the average price of all items from Shaw's and the average price of all items from Meijer.  Then I want the percent difference between all three.  So if the avg for Wal-Mart is 2.82, and Shaw's is 2.76, then the pct diff between Shaw's and Walmart is 2.13%

The other thing is the data is grouped by department.  So I have line items for dairy, bread, paper products, etc.  The same calcuation would have to be done for all of the departments.

Thanks for help on this.

``````Ven_Ind  Vendor     Item                                        Price       Dept_Ind
1             Wal-Mart	Eggs, large, dozen	                3.13       12
1             Wal-Mart	Eggs, medium, dozen	3.05       12
1             Wal-Mart	Cheese, sliced, American	2.89       12
1             Wal-Mart	Cheese, sliced, Swiss	2.74       12
1             Wal-Mart	Sour cream	                2.59       12
1             Wal-Mart	Sour cream,light	                2.52       12
2             Shaw's	Eggs, large, dozen	                3.13       12
2             Shaw's	Eggs, medium, dozen	2.97       12
2             Shaw's	Cheese, sliced, American	2.82       12
2             Shaw's	Cheese, sliced, Swiss	2.67       12
2             Shaw's	Sour cream	                2.52       12
2             Shaw's	Sour cream,light	                2.45       12
3             Meijer	Eggs, large, dozen	                2.45       12
3             Meijer	Eggs, medium, dozen	3.20       12
3             Meijer	Cheese, sliced, American	3.13       12
3             Meijer	Cheese, sliced, Swiss	3.05       12
3             Meijer	Sour cream	                2.97       12
3             Meijer	Sour cream,light	                2.88       12
``````
0
Question by:kenyayvette

LVL 41

Accepted Solution

ID: 24022580
``````select a.vendorid, a.vendor, a.avgprice, b.vendorid, b.vendor, b.avgprice, ((a.avgprice - b.avgprice) / a.avgprice) as ptcdifference from (
select vendorid, vendor, avg(price) as avgprice from yourtable
group by Vendor) a
cross join (select vendorid, vendor, avg(price) as avgprice from yourtable
group by Vendor) b
where a.vendor < b.vendorid
``````
0

LVL 2

Author Comment

ID: 24024086
Thank you so very much, that is exactly what I needed.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
Loops Section Overview
Screencast - Getting to Know the Pipeline
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll