Solved

group by in gridview

Posted on 2007-11-30
6
575 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
List<PaisEntity> - show some 1 38
Json and ajax 1 20
CSS for Popup in ASP.NET 4 15
Broken .resx file generating errors 18 16
Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 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