Solved

group by in gridview

Posted on 2007-11-30
6
578 Views
Last Modified: 2008-02-01
hello, i ve got a gridview with this result:

Customer                CategoryID               Description
-------------------------------------------------------------
Jon Smith                     1                          First Classs - Best Service
Steve Smith                 1                           NULL
Paul Smith                    2                           NULL
Robert Smith                     2                           NULL
David Smith                     2                           NULL

NOw, i want to add another gridview nwxt to it with this result:

Category ID           Count(grouped by categoryID)
-----------------------------------------------------------
       1                         2
       2                         3

My question is.....could i use the same objectdatasource of the first grid to build the second??
What ive tried to do so far is pass the result of the first grid onto a dataview. Then, from the dataview use the 'ToTable method' to a new table. This way i can get the distinct values. But i cant get the count column.
 any ideas? do i have to create another query for this?
 
0
Comment
Question by:arcross
[X]
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
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20381268
Hi, I think that the best way would be to just use a query to your database, what I recommend is having both query's returned via a Stored Procedure, and store them in a DataSet, that way it would only be one trip and each query results would be stored in a different table of the DataSet.
0
 
LVL 8

Author Comment

by:arcross
ID: 20381521
thanks joex911 for yuor suggestion. This is the query ive got to return the count column:

SELECT COUNT(C.CategoryID),C.CategoryName FROM CatServices AS C
JOIN (SELECT CategoryID,CompanyName,Description  FROM Services WHERE
CompanyName LIKE '%%' OR
(Description LIKE '%%' AND NOT DESCRIPTION IS NULL)) AS TEST ON C.CategoryID = TEST.CategoryID
GROUP BY C.CategoryName

Can i pass a parameter in a stored procedure using the LIKE clause.??
eg: @MyFilter

SELECT ID FROM Table1 WHERE Field2 LIKE '%' + @MyFilter + '%'

0
 
LVL 8

Author Comment

by:arcross
ID: 20381653
I tried passing a parameter like the example above and it worked.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 10

Expert Comment

by:Oliver Amaya
ID: 20381697
I'm not sure but I believe this query should return the same results and avoids using a subquery:
SELECT COUNT(C.CategoryID),C.CategoryName FROM CatServices AS C
INNER JOIN Services S ON C.CategoryID = S.CategoryID
WHERE S.CompanyName LIKE '%%'
  OR (S.Description LIKE '%%' AND NOT S.Description IS NULL)
GROUP BY C.CategoryName

Open in new window

0
 
LVL 8

Author Comment

by:arcross
ID: 20381788
yes it does joex. Thanks for that.

But is there a way to use an aggregate function using a datatable that uses grouping and counting?
0
 
LVL 10

Accepted Solution

by:
Oliver Amaya earned 500 total points
ID: 20381838
DataTables support something called Column Expressions, where you define a DataColumn with a value derived from the other set of columns in the query, I've used them before but to concatenate fields, I found a link that says you can also add aggregate functions, but haven't tried it yet.

http://www.informit.com/guides/content.aspx?g=dotnet&seqNum=322&rl=1
// Add OrderCount column to Customers table
tblCustomers.Columns.Add("OrderCount", typeof(int), "Count(Child(CustomerOrders).OrderId)");

Open in new window

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

695 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