Non aggregated fields in the Group By clause

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

Non aggregated fields in the Group By clause

Author

Topic

GC72
Starting Member

16 Posts

Posted - 2013-07-08 : 11:50:13

Hi,
I'd like to have all distinct recordIDs with relevant text associated with them. Each record has 3 text boxes in different languages. Each text in different language is defined by an AttributeDefinitionID. This is my query:

Select a.entryID, g.GroupName, c.CategoryName as ExperienceType,
e.AttributeValue as EnglishWording,
e1.AttributeValue as GermanWording,
e2.AttributeValue as RussianWording,
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid
inner join [Group] as g on g.groupID = c.groupID

left outer join EntryAttribute AS d on d.entryID = A.entryid
left outer join attributestring as e on (e.attributeid = d.attributeid and e.attributedefinitionid = 15)

left outer join EntryAttribute AS d1 on d1.entryID = A.entryid
left outer join attributestring as e1 on (e1.attributeid = d1.attributeid and e1.attributedefinitionid = 41)

left outer join EntryAttribute AS d2 on d2.entryID = A.entryid
left outer join attributestring as e2 on (e2.attributeid = d2.attributeid and e2.attributedefinitionid = 64)
order by a.EntryID asc;

but in the results I get additional rows for each record even if the record doesnt have all three text boxes populated and there is only EnglishText for example.

EntryID GrouPName EnglishWording GermanWording RussianWording
1586 Red abc NULL NULL
1586 Red NULL NULL NULL
3566 Yellow NULL Hallo Welt NULL
3566 Yellow NULL NULL NULL
3566 Yellow Hello world NULL NULL
3566 Yellow Hello world Hallo Welt NULL

1586 should only return the first line with English wording.
3566 should return the last line that shows both English and German wording populated
Much appreciated.
GC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-08 : 12:26:36

[code]
Select a.entryID, g.GroupName,
c.CategoryName as ExperienceType,
e.EnglishWording,
e.GermanWording,
e.RussianWording,
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid
inner join [Group] as g on g.groupID = c.groupID
left outer join EntryAttribute AS d on d.entryID = A.entryid
left outer join (select attributeid,
MAX(CASE WHEN attributedefinitionid = 15 THEN AttributeValue END) AS EnglishWording,
MAX(CASE WHEN attributedefinitionid = 41 THEN AttributeValue END) AS GermanWording,
MAX(CASE WHEN attributedefinitionid = 64 THEN AttributeValue END) AS RussianWording
FROM attributestring
GROUP BY attributeid
)e
on e.attributeid = d.attributeid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-08 : 13:22:31

Thanks. Tried this but it doesnt seem to work. Still getting too many rows returned.?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-08 : 13:57:36

How about this

Select
a.entryID
,g.GroupName,
c.CategoryName as ExperienceType,
MAX(CASE WHEN e.attributedefinitionid = 15 THEN AttributeValue END) AS EnglishWording,
MAX(CASE WHEN e.attributedefinitionid = 41 THEN AttributeValue END) AS GermanWording,
MAX(CASE WHEN e.attributedefinitionid = 64 THEN AttributeValue END) AS RussianWording
--e.EnglishWording,
--e.GermanWording,
--e.RussianWording,
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid
inner join [Group] as g on g.groupID = c.groupID
left outer join EntryAttribute AS d on d.entryID = A.entryid
left outer join attributestring AS e on e.attributeid = d.attributeid
Group by a.entryID,g.GroupName,c.CategoryName

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:30:43

quote:
Originally posted by GC72

Thanks. Tried this but it doesnt seem to work. Still getting too many rows returned.?

i think it may that the relationship with Enitity attribute itself is one to many

try this modification


Select a.entryID, g.GroupName, 
c.CategoryName as ExperienceType, 
att.EnglishWording,
att.GermanWording,
att.RussianWording
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid 
inner join [Group] as g on g.groupID = c.groupID
left outer join (select d.entryID,
                 MAX(CASE WHEN attributedefinitionid = 15 THEN AttributeValue END) AS EnglishWording, 
                 MAX(CASE WHEN attributedefinitionid = 41 THEN AttributeValue END) AS GermanWording, 
                 MAX(CASE WHEN attributedefinitionid = 64 THEN AttributeValue END) AS RussianWording 
                 FROM attributestring e
                 INNER JOIN EntryAttribute AS d 
                 on e.attributeid = d.attributeid                 
                 GROUP BY d.entryID 
                )att
on att.entryID = A.entryid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-09 : 10:29:34

Thank you both. Both return the same. I still get multiple rows because of the ExperienceTypes (CategoryName) as this is one to many for most entryIDs.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 10:55:06

exclude it (CategoryName) from the column list of SELECT statement and see if then it shows the desired data?

Cheers
MIK

GC72
Starting Member

16 Posts

Posted - 2013-07-09 : 12:46:25

I have joined other tables and added more to the select statement, but because the new fields are not included in the Group by clause, its returning an error. But only want to group by EntryID and GroupName and not all other fields. Is there a way around this please?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 12:58:18

How about excluding the CategoryName from select columns list and Group by Clause? did it give you the desired result?

quote:

I have joined other tables and added more to the select statement, but because the new fields are not included in the Group by clause, its returning an error. But only want to group by EntryID and GroupName and not all other fields. Is there a way around this please?

You have to include all the non-aggregated columns in the group by clause to full fill the grouping concept..
So if I say
SELECT col1,col2,count(col3)
From table1
Group By Col1

logically won't make any sense, since col1 and col2 are non aggregated columns and are meant to be a group.

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 14:06:27

quote:
Originally posted by GC72

I have joined other tables and added more to the select statement, but because the new fields are not included in the Group by clause, its returning an error. But only want to group by EntryID and GroupName and not all other fields. Is there a way around this please?

That doesnt make any sense
as if you just group by EntryID and GroupName there will be multiple CategoryName values in each of those groups. Then obvious question would be which value out of that you want to return ie min,max etc as you can return only single value once you apply group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-10 : 05:44:37

I have excluded the CategoryName from the query. I am now only querying for single value fields. I need to include these fields in the select list.

Select a.entryID,g.GroupName,
MAX(CASE WHEN e.attributedefinitionid = 15 THEN e.AttributeValue END) AS EnglishWording,
MAX(CASE WHEN e.attributedefinitionid = 41 THEN e.AttributeValue END) AS GermanWording,
MAX(CASE WHEN e.attributedefinitionid = 64 THEN e.AttributeValue END) AS RussianWording,
MAX(CASE WHEN e.attributedefinitionid = 91 THEN e.AttributeValue END) AS FrenchWording,
MAX(CASE WHEN e.attributedefinitionid = 143 THEN e.AttributeValue END) AS ItalianWording,
MAX(CASE WHEN e.attributedefinitionid = 160 THEN e.AttributeValue END) AS SpanishWording,
MAX(CASE WHEN e.attributedefinitionid = 165 THEN e.AttributeValue END) AS ChineseWording,
MAX(CASE WHEN e.attributedefinitionid = 170 THEN e.AttributeValue END) AS JapaneseWording,

MAX(CASE WHEN h.attributedefinitionid = 17 THEN convert(varchar(10),h.AttributeValue,120) END) as DateAnnounced,
MAX(CASE WHEN h.attributedefinitionid = 557 THEN convert(varchar(10),h.AttributeValue,120) END) as DateOpen,
MAX(CASE WHEN h.attributedefinitionid = 558 THEN convert(varchar(10),h.AttributeValue,120) END) as DateClosed,

j.attributevalue as HealthWarning,
l.attributevalue as Highlight,
n.attributevalue as GlobalInvestigations,
p.attributevalue as Comments,
a.created, a.modifed
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid
inner join [Group] as g on g.groupID = c.groupID
left outer join EntryAttribute AS d on d.entryID = A.entryid
left outer join attributestring AS e on e.attributeid = d.attributeid

left outer join EntryAttribute as f on f.EntryID = a.EntryID
left outer join AttributeDateTime as h on h.AttributeID = f.AttributeID

left outer join EntryAttribute as i on i.EntryID = a.EntryID
left outer join AttributeString as j on j.AttributeID = i.AttributeID

left outer join EntryAttribute as k on k.EntryID = a.EntryID
left outer join AttributeString as l on l.AttributeID = k.AttributeID

left outer join EntryAttribute as m on m.EntryID = a.EntryID
left outer join AttributeString as n on n.AttributeID = m.AttributeID

left outer join EntryAttribute as o on o.EntryID = a.EntryID
left outer join AttributeString as p on p.AttributeID = o.AttributeID

where j.AttributeDefinitionID = 29
and l.AttributeDefinitionID = 23
and n.attributeDefinitionID = 572
and n.attributeDefinitionID = 30
Group by a.entryID,g.GroupName

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-10 : 06:53:21

Okay so you excluded the CategoryName in your above query and that gave you the desired result.

So if you've missed then see Visakh last response for adding other non-aggregated fields. If you're adding non aggregated fields to this query which would mean that you want them to part of group and so needs to be added into the group by clause. Now if those non aggregated fields have one to many relationship e.g. multiple (categories/)values for same entryID then obviously it will return multiple records which is the problem for you. In order to cater that you need to identify which specific category/value needs to be pulled? Once that is identified you can have a solution for it.

Note: Instead of using the repeating left outer join for the two tables for each CASE (which is already handled in SELECT), its better to use it once (as Visakh suggested in his responses or I did in my one) for performance puposes.

Cheers
MIK

GC72
Starting Member

16 Posts

Posted - 2013-07-10 : 07:29:00

Thanks MIK. My query doesnt return multiple records as I am not including the one to many relationships in this query. The problem I have is with non aggregated fields in the select list. Because there are quite a few fields, I was not sure whether adding them all into the Group by clause would cause any problems. I have just added all fields to the group by clause and it returned only 19 records where the number of records should be 23,000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 08:40:54

Unless you define what exactly you're looking at, its difficult for someone to help you. Based on your requirement you need to apply GROUP BY only on necessary columns and should apply aggregates over others. And you'll be able to return one and only one value for aggregated fields when you group on a column combination which may be sum, average, min, max, first or last value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-10 : 09:51:12

When I run the last query I pasted above without any of the following in the select query (commented out with --), it returns correct number of records with all different wording languages. I dont need to group by created or modified dates but i had to add these to group by clause in order to execute the query, which returns ok. But I can't add all the other fields (j.attributevalue as HealthWarning,
l.attributevalue as Highlight,
n.attributevalue as GlobalInvestigations,
p.attributevalue as Comments,) to the select list and the group by clause as this doesnt return the correct number of records

Select a.entryID,g.GroupName,
MAX(CASE WHEN e.attributedefinitionid = 15 THEN e.AttributeValue END) AS EnglishWording,
MAX(CASE WHEN e.attributedefinitionid = 41 THEN e.AttributeValue END) AS GermanWording,
MAX(CASE WHEN e.attributedefinitionid = 64 THEN e.AttributeValue END) AS RussianWording,
MAX(CASE WHEN e.attributedefinitionid = 91 THEN e.AttributeValue END) AS FrenchWording,
MAX(CASE WHEN e.attributedefinitionid = 143 THEN e.AttributeValue END) AS ItalianWording,
MAX(CASE WHEN e.attributedefinitionid = 160 THEN e.AttributeValue END) AS SpanishWording,
MAX(CASE WHEN e.attributedefinitionid = 165 THEN e.AttributeValue END) AS ChineseWording,
MAX(CASE WHEN e.attributedefinitionid = 170 THEN e.AttributeValue END) AS JapaneseWording,

MAX(CASE WHEN h.attributedefinitionid = 17 THEN convert(varchar(10),h.AttributeValue,120) END) as DateAnnounced,
MAX(CASE WHEN h.attributedefinitionid = 557 THEN convert(varchar(10),h.AttributeValue,120) END) as DateOpen,
MAX(CASE WHEN h.attributedefinitionid = 558 THEN convert(varchar(10),h.AttributeValue,120) END) as DateClosed,

--j.attributevalue as HealthWarning,
--l.attributevalue as Highlight,
--n.attributevalue as GlobalInvestigations,
--p.attributevalue as Comments,
convert(varchar(10),a.created,120) as CreatedOn,
convert(varchar(10),a.modifed,120) as ModifiedOn
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid
inner join category as c on c.categoryid= b.categoryid
inner join [Group] as g on g.groupID = c.groupID
left outer join EntryAttribute AS d on d.entryID = A.entryid
left outer join attributestring AS e on e.attributeid = d.attributeid

left outer join EntryAttribute as f on f.EntryID = a.EntryID
left outer join AttributeDateTime as h on h.AttributeID = f.AttributeID

--left outer join EntryAttribute as i on i.EntryID = a.EntryID
--left outer join AttributeString as j on j.AttributeID = i.AttributeID

--left outer join EntryAttribute as k on k.EntryID = a.EntryID
--left outer join AttributeString as l on l.AttributeID = k.AttributeID

--left outer join EntryAttribute as m on m.EntryID = a.EntryID
--left outer join AttributeString as n on n.AttributeID = m.AttributeID

--left outer join EntryAttribute as o on o.EntryID = a.EntryID
--left outer join AttributeString as p on p.AttributeID = o.AttributeID

--where j.AttributeDefinitionID = 29
--and l.AttributeDefinitionID = 23
--and n.attributeDefinitionID = 572
--and p.attributeDefinitionID = 30

Group by a.entryID,g.GroupName, a.created, a.modifed
--j.attributevalue,
--l.attributevalue,
--n.attributevalue,
--p.attributevalue

I hope this makes sence. Sorry for the lack of explanation and confusion.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 10:57:48

you dont need j.attrbutevalue etc columns. As I showed you write them as MAX(CASE WHEN...) conditions just like the current columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-10 : 12:36:50

I wrote them all as MAX(CASE WHEN) conditions and it is still executing after an hour?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-10 : 12:59:40

quote:
Originally posted by GC72

I wrote them all as MAX(CASE WHEN) conditions and it is still executing after an hour?!

check the execution plan and see the costly steps

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

GC72
Starting Member

16 Posts

Posted - 2013-07-15 : 11:44:38

All working ok now after deleting the multiple joins! Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 00:43:53

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources