Grouping using compute

Question

Post reply

Grouping using compute

Rajesh Chandra

Valued Member

Points: 62
More actions
July 25, 2005 at 8:42 am

#186709

Hi All,
          My Actual requirement is to group the data and show it in a datagrid.

           like
              country             state                 sum of population
             ---------           ------                ------------------
             india                  ap                     10
              india                   tn                     20

no of records in the above group 2

             us                        cal                    30
             us                       some state          40

no of records in the above group 2
etc..

i don't how many groups will be there.

for this i am using a stored procedure. in the stored procedure i am using the compute by clause to get the desired output.

but the problem is the compute by clause returns more than one result set.

how to work with more than one result set and combine them into one result set.
i want only one result set out of the stored proc.
here the single select will return multiple resultsets.

or is there any other way to acheive the same thing.

hope u got the problem. pls reply with a solution.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

~~Workaround~~

~~SELECT * INTO #Result~~

~~'Your Query with COMPUTE BY'~~

~~SELECT * FROM #Result~~

SELECT * INTO #Result

'Your Query with Rollup'

Edit/delete and update #Result

SELECT * FROM #Result

Regards,
gova

noeld SSC Guru Points: 96590 More actions · Answer 2

it is not very difficult to work with more than one result set just make sure that when you are done with one you call NextRecordSet from the ADO recorset object or NextResult from the DataReader in ADO.NET!

Now the solution from TSQL perpective is to use WITH ROLLUP clause after the group by. Have alook at it in BOL

* Noel

Rajesh Chandra Valued Member Points: 62 More actions · Answer 3

Hi Noel,

Thanks for your reply. but the prob is actually i should show this in a datagrid object in asp.net.

Also the data is very huge. I have tried rollup, but the output has extra records for null case.

cant we work on the resultsets in the sql and get a single result set. is it possible.

Rajesh Chandra Valued Member Points: 62 More actions · Answer 4

Hi govinn,

Thanks for your reply.

SELECT * INTO #Result

'Your Query with COMPUTE BY'

SELECT * FROM #Result

but i am getting an error.

Insert Error: Column name or number of supplier values does not match table definition.

have you tried the above query.

noeld SSC Guru Points: 96590 More actions · Answer 5

>> I have tried rollup, but the output has extra records for null case. <<

You can filter those out in your Where Clause

>>cant we work on the resultsets in the sql and get a single result set. is it possible. <<

Didn't you said that you used WITH ROLLUP? It does returns all in a single result set!

>>but the prob is actually i should show this in a datagrid object in asp.net<<

What is the big deal? You are returning two sets of completly different data if you bind one result set to one grid and the other to another you are done! or better yet, if you store them in a dataset with the Appropriate relations then a single bind will be necessary

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 6

I thougt I used rollup in my reply.

try

SELECT * INTO #Result

'Your Query with Rollup'

Edit/delete and update #Result

SELECT * FROM #Result

Or you can merge the multiple datatables into one datatable in the front end before binding the data to the datagrid.

http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataDataSetClassMergeTopic.asp?frame=true

Regards,
gova

Rajesh Chandra Valued Member Points: 62 More actions · Answer 7

hi,

there is no chance for creating relations, because we will only get different groups of data. and i have to show only in one grid.

noeld SSC Guru Points: 96590 More actions · Answer 8

Rajesh,

If you are going to use only one grid why do you need the record count in a compute by?

or is it that the record count should be another column?

* Noel

Rajesh Chandra Valued Member Points: 62 More actions · Answer 9

Hi Noel,

I want only in one grid and i want the count of records in each group because the CLIENT wants it that way.

actually this data in datagrid is then exported into excel.

Rajesh Chandra Valued Member Points: 62 More actions · Answer 10

Hi Noel,

I want only in one grid and i want the count of records in each group because the CLIENT wants it that way.

actually this data in datagrid is then exported into excel.