Solved

group by in gridview

Posted on 2007-11-30
6
570 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
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:joex911
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 10

Expert Comment

by:joex911
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:
joex911 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now