I currently have some code that gets the overall top 25 frame sales, which is as follows:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

What I would also like to do is get the top 25 frame sales for each DISTINCT company and group the results by company (and order by count). I tried the following but I am not having any luck:

Dim FrameCountSelectStr As String = "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName FROM(Glasses) WHERE (SELECT DISTINCT Glasses.Manufacturer FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Maybe I am just tired or getting confused, but any pointers in the right direction would be much appreciated.

Recommended Answers

All 6 Replies

I will give that article a read and see if I can come up with a solution.

I was able to perform the task in 2 separate SELECT queries but it is pretty slow to load all of the data the way I came up with. I am pretty sure it can be combined into 1 quick efficient query but I am not able to get any combination working.

My first SELECT statement is: "SELECT DISTINCT Manufacturer FROM Glasses WHERE FrameName <> '' AND Manufacturer <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Dispenser ='" & ComboBox1.Text & "' ORDER BY Manufacturer ASC"

I then iterate through the returned dataset rows: For Each row As DataRow In ManuDS.Tables(0).Rows

And throw this SELECT statement in there: "SELECT TOP 25 Count(Glasses.OrderID) AS GlassesCount, Glasses.Manufacturer, Glasses.FrameName, Glasses.Colour, Glasses.ADim FROM(Glasses) WHERE FrameName <> '' AND OrderDate >= #" & SearchDateStart & "# AND OrderDate < #" & SearchDateEnd & "# AND Manufacturer = '" & row.Item("Manufacturer").ToString & "' AND Dispenser ='" & ComboBox1.Text & "' GROUP BY Glasses.Manufacturer, Glasses.FrameName ORDER BY Count(Glasses.OrderID) DESC"

Again, this gets the job done, but it does take some time to load all of the data as I am running this query for multiple Manufacturers.

Any help on getting this into 1 efficient query would be much appreciated.

Manufacturers is not indexed. I will definitely do that to improve performance. Thank you for all of your help.

You should consider using parameterized queries. Not only are they more secure, they are much easier to read and debug.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.