group by in gridview

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?
 
LVL 8
arcrossAsked:
Who is Participating?
 
Oliver AmayaConnect With a Mentor EntrepeneurCommented:
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
 
Oliver AmayaEntrepeneurCommented:
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
 
arcrossAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
arcrossAuthor Commented:
I tried passing a parameter like the example above and it worked.
0
 
Oliver AmayaEntrepeneurCommented:
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
 
arcrossAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.