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
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error message when scheduling a job using a linked Server 12 60
Selection from table2 where criteria for table1 10 41
Sql query 107 56
Query to return total 6 17
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now