Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

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?
 
0
arcross
Asked:
arcross
  • 3
  • 3
1 Solution
 
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
 
arcrossAuthor Commented:
I tried passing a parameter like the example above and it worked.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
Oliver AmayaEntrepeneurCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now