Hello all!
I know the following will work,
"SELECT DISTINCT Name, MIN(Sign) AS Sign
FROM Profile
GROUP BY Name"
Will return 2 columns, Name and Sign.
But what if I want more than just the two columns, and I need four to be
listed, but using the same code above. Just not sure how to add additional
columns without getting errors. Is this even possible?
TIA!!!
RudyIf you can show us some sample data and the required output we can come up
with some queries. Without that, you either add those additional columns to
the GROUP BY clause, or have then in the SELECT, within an aggregate
function.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:5EC4A04B-AD95-40C6-BBEE-D8A1E9B3BF52@.microsoft.com...
Hello all!
I know the following will work,
"SELECT DISTINCT Name, MIN(Sign) AS Sign
FROM Profile
GROUP BY Name"
Will return 2 columns, Name and Sign.
But what if I want more than just the two columns, and I need four to be
listed, but using the same code above. Just not sure how to add additional
columns without getting errors. Is this even possible?
TIA!!!
Rudy|||Yes, you need to decide, for each of those other columns, which of the
possible multiple values that exists should be output by the query...
Since you are Grouping By Name, that means you will get one row in your
output per disntinct value of Name. There may be many rows in the original
Table for each value of Name, each with different values for these other
columns... So for each, you must tell query whether to output the Min(), the
Max(), the Sum(), AVG(), or whatever...
Select Name, MIN(Sign) AS Sign,
Min(Col1), Max(Col2), etc...
From Profile
Group By Name
If you want ALL the values of these other columns listed, as:
Name Col1 Col2
John 1 AA
John 2 AB
John 3 AC
etc.
then you can't group just by name, you need to add the other columns to the
group By clause
"Rudy" wrote:
> Hello all!
> I know the following will work,
> "SELECT DISTINCT Name, MIN(Sign) AS Sign
> FROM Profile
> GROUP BY Name"
> Will return 2 columns, Name and Sign.
> But what if I want more than just the two columns, and I need four to be
> listed, but using the same code above. Just not sure how to add additional
> columns without getting errors. Is this even possible?
> TIA!!!
> Rudy|||also, look at the with rollup and with group options for group, then you can
do stuff like:
Select Name, col1, min(Sign)
From Profile
Group By Name, col1 with rollup
and you will get all sorts of different levels...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:406DA4BA-6217-431D-B838-CA56B96C3453@.microsoft.com...
> Yes, you need to decide, for each of those other columns, which of the
> possible multiple values that exists should be output by the query...
> Since you are Grouping By Name, that means you will get one row in your
> output per disntinct value of Name. There may be many rows in the original
> Table for each value of Name, each with different values for these other
> columns... So for each, you must tell query whether to output the Min(),
> the
> Max(), the Sum(), AVG(), or whatever...
> Select Name, MIN(Sign) AS Sign,
> Min(Col1), Max(Col2), etc...
> From Profile
> Group By Name
> If you want ALL the values of these other columns listed, as:
> Name Col1 Col2
> John 1 AA
> John 2 AB
> John 3 AC
> etc.
> then you can't group just by name, you need to add the other columns to
> the
> group By clause
>
> "Rudy" wrote:
>|||Thanks you everyone for your suggestions! CBretana, your answer did the
trick. Thank!!!
Rudy
"CBretana" wrote:
> Yes, you need to decide, for each of those other columns, which of the
> possible multiple values that exists should be output by the query...
> Since you are Grouping By Name, that means you will get one row in your
> output per disntinct value of Name. There may be many rows in the original
> Table for each value of Name, each with different values for these other
> columns... So for each, you must tell query whether to output the Min(), t
he
> Max(), the Sum(), AVG(), or whatever...
> Select Name, MIN(Sign) AS Sign,
> Min(Col1), Max(Col2), etc...
> From Profile
> Group By Name
> If you want ALL the values of these other columns listed, as:
> Name Col1 Col2
> John 1 AA
> John 2 AB
> John 3 AC
> etc.
> then you can't group just by name, you need to add the other columns to th
e
> group By clause
>
> "Rudy" wrote:
>
