The Group By Clause to return more fileds

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

General SQL Server Forums

New to SQL Server Programming

The Group By Clause to return more fileds

Author

Topic

mahesh6ag
Starting Member

1 Post

Posted - 2011-04-10 : 02:13:59

Dear SQL Experts

I am new to the SQL and need your help to solve my problem

I have a following table named stress with 5 fileds, and 75 records

I use the the following SQL to retutn the maximum stress ratio from ratio field, and group by the combination, this what I was looking for , but I am unable to get the other corresponding fields from the table,example the Category, Stress, Allowable for these filtered values

SQL Statement : SELECT combination,max(ratio) FROM stress GROUP BY combination
Result

Combination |Ratio
Amb to T1 0.7887792
Amb to T2 0.061314866
GR + Max P 0.76127881
Max P 0.78806025
TR:T1 to T2 0.85066134

If I include any other column in the SELECT, it gives me error, ex
SELECT Category, combination,max(ratio) FROM stress GROUP BY combination = error

and if I include column in groupby clause the results are entirely different, Please help me. I tried using Join, or subquery it dosn't help.

Regards
Mahesh

SAMPLE TABLE

Combination |Category |Stress |Allowable |Ratio
Max P Hoop 1.49E+04 1.89E+04 7.88E-01
GR + Max P Sustain 8.57E+03 1.89E+04 4.54E-01
TR:T1 to T2 Expansion 5.78E+02 2.97E+04 1.95E-02
Amb to T1 Expansion 5.36E+02 2.97E+04 1.80E-02
Amb to T2 Expansion 4.21E+01 3.00E+04 1.40E-03
Max P Hoop 1.49E+04 1.89E+04 7.88E-01
GR + Max P Sustain 7.27E+03 1.89E+04 3.85E-01
TR:T1 to T2 Expansion 2.93E+02 2.97E+04 9.85E-03
Amb to T1 Expansion 2.72E+02 2.97E+04 9.13E-03
Amb to T2 Expansion 2.13E+01 3.00E+04 7.10E-04
Max P Hoop 1.49E+04 1.89E+04 7.88E-01
GR + Max P Sustain 1.44E+04 1.89E+04 7.61E-01
TR:T1 to T2 Expansion 1.16E+03 2.97E+04 3.92E-02
Amb to T1 Expansion 1.08E+03 2.97E+04 3.63E-02
Amb to T2 Expansion 8.47E+01 3.00E+04 2.82E-03
Max P Hoop 1.49E+04 1.89E+04 7.88E-01
GR + Max P Sustain 6.78E+03 1.89E+04 3.59E-01
TR:T1 to T2 Expansion 7.09E+03 2.97E+04 2.39E-01
Amb to T1 Expansion 6.58E+03 2.97E+04 2.21E-01
Amb to T2 Expansion 5.16E+02 3.00E+04 1.72E-02
Max P Hoop 1.49E+04 1.89E+04 7.88E-01
GR + Max P Sustain 7.54E+03 1.89E+04 3.99E-01
TR:T1 to T2 Expansion 1.86E+04 2.97E+04 6.25E-01
Amb to T1 Expansion 1.72E+04 2.97E+04 5.80E-01
Amb to T2 Expansion 1.35E+03 3.00E+04 4.51E-02

]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 05:55:51

In most cases, when you have an aggregation function (like max, sum, avg etc.) in the select list, you will need to include any other columns in your select in the group by clause.

So if you want to add Category in the select list, you must include that in the group by as well. But then, the max value you get is NOT the max within a combination. It is the max within the combination AND Category.

A solution for this, if you are on SQL 2005 or higher, is to use windowing function like this:


select
   combination,
   max(ratio) over (partition by combination) as MaxRatio,
   Category,
   Stress
   --, any other columns
from
   stress

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources