[RESOLVED] help with mssql query to return top five selling products by date

[iced8out]

so far i've managed the code to load the results to gridview. Can anyone help me by grouping the products by productname and qty sold for each product. my goal is to see top 100 products and the qty's sold within 2 dates. here is my current code:

Try
            Cursor = Cursors.WaitCursor
            Timer1.Enabled = True
            con = New SqlConnection(cs)
            con.Open()
            cmd = New SqlCommand("Select RTRIM(InvoiceNo),InvoiceDate,RTRIM(ProductName),RTRIM(Invoice_Product.Barcode),Invoice_Product.Qty,Invoice_Product.SalesRate,Invoice_Product.PurchaseRate, Invoice_Product.DiscountPer, Invoice_Product.Discount, Invoice_Product.VATPer, Invoice_Product.VAT, Invoice_Product.TotalAmount,Invoice_Product.Margin,Temp_stock_Company.Qty from InvoiceInfo,Invoice_Product,Product,Temp_Stock_Company where InvoiceInfo.Inv_ID=Invoice_product.InvoiceID and product.PID=Invoice_Product.ProductID and Product.PID=temp_Stock_Company.ProductID and InvoiceDate >=@d1 and InvoiceDate < @d2 Order by InvoiceDate", con)
            cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
            cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date.AddDays(1)
            cmd.CommandTimeout = 0
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            dgw.Rows.Clear()
            While (rdr.Read() = True)
                dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10), rdr(11), rdr(12), rdr(13))
            End While
            con.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

[Shaggy Hiker]

Moved.

[jmcilhinney]

If the issue has been resolved then it would be nice for you to provide your solution, which may help others with similar issues. It also means that we can provide improvements if your solution is suboptimal.

[iced8out]

no the issue is still not resolved. its loading the gridview but with each not merging the rows. example receipt 1: sold bmx bike for $99, receipt 2: sold bmx helmet for $40, receipt 3: sold bmx bike for $99 etc.. in gridview its showing every receipt as a row.. instead of **row-1: 2 bmx bikes for $198, **row-2: 1 bmx helmet $40

[Zvoni]

Your Problem is that you're not aggregating the fields and you're using unique-per-row fields in your Output (e.g. InvoiceNo)