One Question about Groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One Question about Groups

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-10-08 : 04:24:46

What needs to be in the select clause
and in the group by clause?

Does every field in the select clause HAVE to be in the group by clause

OR vice versa

does every field in the group by clause HAVE to be in the select clause?

OR a subclause of the other...

OR

is it all about nested queries, in which you group by one field and select one, maybe aggregate one item, give that a name, and pass that result back to the outer query, and then repeat this level of grouping in any number of queries, where the final query supplies a column for each group, joining each group query as a derived table?

The reason why i ask is that I've been using Crystal Reports & SSRS for so many years, which does all this grouping for me at report level, so I can just produce a dataset with no grouping or order or having clauses. Maybe this is good or bad for performance overall, with the Crystal app doing the calculations rather than the server.

I have done all the above - it's time to get this straight, and get all the work done in SQL?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 04:38:48

always whatever field you're using straight in select clause without any aggregation applied like MAX(),SUM() etc have to be included in GROUP BY. the inverse is not necessary. you can have field in group by but ignore it in select if don't want to display its values, but keep in mind that displayed values will still be based on its individual values also as its a part of the group. The reason why its like this is because once you group on particular set of values what you're virtually trying to do is to return 1 record per each unique combination of values of fields in group by so obviously there will be multiple values existing for the other non grouped fields of which you cant return all. Thats why it mandates you the use of aggregated functions which will determine what value you require out of the set of values ie. whether its max of them, min of them, sum of them, average of them etc.
Going one step ahead you can even select the values out of group based on a further conditional expression like minimum value out of non zero values, maximum value greater than threshold etc. thats when you use CASE WHEN inside MIN,MAX etc like


...
Col1,
MIN(CASE WHEN Col2>0 THEN Col2 ELSE NULL END),
MAX(CASE WHEN Col3>50 THEN Col3 ELSE NULL END),
SUM(CASE WHEN Col6='Some val' AND Col7> 100 THEN Col11 ELSE 0 END)
FROM table
GROUP BY Col1

in case of reports. if you've static grouping its probably best to do grouping in sql and bring it in reports and display it as per your format. Aggregation operations are best performed in DB engine than in reports. But in some cases you might have to do different levels of grouping or dynamic grouping based on parameters thats when you bring the data as it is and then do grouping in reports with small performance hit. In such cases also for optimised performance do all filterations at db level and bring only required subset of data in report before applying the required level of grouping.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-10-08 : 06:13:54

The rollup and cube functions, or pivot, are what reports can do away from the server, and while parameters can be in a stored procedure, or at the report level, and a drilldown required all data, not wrapped-up in aggregates by the server. I think it all depends on the scale of data we deal with. Server just supplying a simple rowset should take the load off. The report would start with a summary, and the end-user chooses where to drill down, ie. the customer. I think SSRS is very dependent on the RS engine. Crystal can put all the load on ORACLE servers.. or the MS DB engine, or use a decent multicore on the user's machine...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 07:01:58

definitely it depends on scale of data. doing too many grouping in reports with large amount of data can increase report loading time. What we do in such cases is to create denormalised tables specifically for the report and prepopulate it with required amount of data so that report will be able to take required data by minimum of joins and filtering. But again with highly dynamic requirements sometimes even this is not possible.Even for summary drilldown reporting structure, we create summary data from db itself and show it in report. for drilldown for the cells clicked by user we will have detail procedure which will take in all value of fields in matrix (row/column) where value comes and render detail data. Even that would be much better that bringing all detail data from db before hand. In practical cases, any users wont be interested to drilldown more than certain % of fields rendered by summary report.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-08 : 09:49:49

Hi,

Having Specifies the search condition for the group to meet. The search condition can use aggregate and nonaggregate expressions. The only columns that can be used in the nonaggregate expressions are those specified as grouping columns in the GROUP BY clause. This is because the group-by columns represent common properties for the entire group. Likewise, the aggregate expressions represent a common property for the entire group. The HAVING clause search condition is expressing a predicate over the properties of the group.

Please refer: http://msdn.microsoft.com/en-us/library/ms173260.aspx

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-10-08 : 14:08:22

This is all really important! Thanks for the answers!

It is time to consider denormalization. I have received flat file tables, to load, breaking into narrower tables, using SSIS. I just thought a design would be set in stone, with each table set up for OLTP input, and a report dataset all about linking many tables to get exactly you want out.

So do you mean achieving the right query, and then loading the rows into a single table for the report? An 'extract'? Hmmm. I like it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 14:13:48

yep..exactly. that really depends on your current query response times.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources