January 13, 2008 at 7:19 pm
If I group by MyTableID how do I get 1ML, 2ML, 3ML and 4ML into the output? The ML columns are datatype varchar. This statement throws an error.
SELECT MyTableID, [1ML], [2ML], [3ML], [4ML] FROM MyTable GROUP BY MyTableID
January 13, 2008 at 8:04 pm
You cannot output columns that are not in the GROUP BY unless they are in an aggregate function... read Books Online for the proper use of GROUP BY.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 13, 2008 at 8:09 pm
I see. Missed when i first read it. thanks
January 13, 2008 at 8:16 pm
Usually a group by has an aggregate in it (SUM, COUNT,etc). Otherwise you're not really grouping by anything.
January 13, 2008 at 9:35 pm
Good point.
This remindes me of a related question i have had. How to return additional fields that are not in the DISTINCT field list?
If I have data that looks like this.
ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math
1………………….Frank………….Johnson……..English
SELECT DISTINCT ProductID, First_Name, Last_Name will return…
ProductID………First_Name……Last_Name
1………………….Frank………….Johnson
SELECT DISTINCT ProductID, First_Name, Last_Name, Major will return…
ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math
1………………….Frank………….Johnson……..English
If I did not care what the major is I just want one listed, how could I get a return like…
ProductID………First_Name……Last_Name…..Major
1………………….Frank………….Johnson………Math
Distinct first and last with additional fields not part of the distinct? I am guessing something like this?
SELECT a.ProductID, a.First_Name, a.Last_Name, a.Major
FROM MyTable a
WHERE (SELECT DISTINCT MyTable ProductID, First_Name, Last_Name)
January 14, 2008 at 4:20 am
With the sample data provided you could use MAX or MIN functions to return one record per first and last name:
SELECT First_Name, Last_Name, MAX(major)
FROM myTable
GROUP BY first_name, last_name
This would return Frank, Johnson, and Math as it would sort the major column alphabetically and return the last value in the sorted list. Zoology would win hands down! Similarly MIN would return English as it would return the first major in the alphabetically sorted list.
You would have to watch out for people possibly having the same first and last names.
If you wanted include the productID in the query above you would get your original return with two records showing both English and Math majors. To return the productID using the MAX or MIN query styles you'd have to:
SELECT myTable.productID, derivedMajor.first_name, derivedMajor.last_name, derivedMajor.major
FROM
(
SELECT First_Name, Last_Name, MAX(major) AS major
FROM myTable
GROUP BY first_name, last_name
)derivedMajor
INNER JOIN myTable
ON derivedMajor.first_name = myTable.first_name
AND derivedMajor.last_name = myTable.last_name
AND derivedMajor.major = myTable.major
Note that you have to include an alias for the MAX column in the derived table. Also, when working with people as an entity it's often best to have a table identifying people separately; for instance tblCustomers would look like:
customerID, first_name, last_name
1, Frank, Johnson
2, John, Smith
3, etc, etc
You can then use the unique customerID in your queries to ensure that the problems presented by having two people with similar names are negated.
HTH
January 14, 2008 at 4:19 pm
Took a little study for me to understand what you did but i get it. 🙂 Thanks.
January 15, 2008 at 9:28 am
You can also "see" the non-grouped columns within a given group if you can specifically select for them with a known column value within an aggregate operator. So, if each Major were numbered within its group, you could...
If object_id('tempdb..#MyTable') is not null drop table #MyTable
Select 1[ProductId],'Frank'[FirstName],'Johnson'[LastName],'Math'[Major],1[Seq] into #MyTable
Union all Select 1,'Frank','Johnson','English',2
Union all Select 1,'John','Smith','History',1
Union all Select 1,'John','Smith','Math',2
Select * from #MyTable
Select
ProductId,
FirstName,
LastName,
Max(Case Seq when 1 then Major+' ' else '' End)+
Max(Case Seq when 2 then Major+' ' else '' End)+
Max(Case Seq when 3 then Major+' ' else '' End)+
Max(Case Seq when 4 then Major+' ' else '' End)+
Max(Case Seq when 5 then Major+' ' else '' End)
from #MyTable
group by ProductId,FirstName,LastName
Or, instead of concatenating to a single column, change the '+' to a comma and get the individual Majors in a separate column within the same row...
The key is that you have to have a known column value within the group to select on with a Case statement.
Play around with it... it can be quite quirky (try changing the Max to Min...)
January 15, 2008 at 9:52 am
That is interesting. I'll play around with it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply