How to loop through a dataset using t-sql

Posted on 2009-03-30
Last Modified: 2012-05-06
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

Open in new window

Question by:kenyayvette
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 41

Accepted Solution

ralmada earned 500 total points
ID: 24022580
Please try this:
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

Open in new window


Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question