SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Non aggregated fields in the Group By clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GC72
Starting Member

16 Posts

Posted - 07/08/2013 :  11:50:13  Show Profile  Reply with Quote
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

Edited by - GC72 on 07/10/2013 10:06:07

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/08/2013 :  12:26:36  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/08/2013 :  13:22:31  Show Profile  Reply with Quote
Thanks. Tried this but it doesnt seem to work. Still getting too many rows returned.?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/08/2013 :  13:57:36  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/09/2013 :  01:30:43  Show Profile  Reply with Quote
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/09/2013 :  10:29:34  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/09/2013 :  10:55:06  Show Profile  Reply with Quote
exclude it (CategoryName) from the column list of SELECT statement and see if then it shows the desired data?

Cheers
MIK
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/09/2013 :  12:46:25  Show Profile  Reply with 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?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/09/2013 :  12:58:18  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/09/2013 :  14:06:27  Show Profile  Reply with Quote
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/10/2013 :  05:44:37  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/10/2013 :  06:53:21  Show Profile  Reply with Quote
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/10/2013 :  07:29:00  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  08:40:54  Show Profile  Reply with Quote
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

Edited by - visakh16 on 07/10/2013 08:41:57
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/10/2013 :  09:51:12  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  10:57:48  Show Profile  Reply with Quote
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/10/2013 :  12:36:50  Show Profile  Reply with Quote
I wrote them all as MAX(CASE WHEN) conditions and it is still executing after an hour?!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/10/2013 :  12:59:40  Show Profile  Reply with Quote
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
Go to Top of Page

GC72
Starting Member

16 Posts

Posted - 07/15/2013 :  11:44:38  Show Profile  Reply with Quote
All working ok now after deleting the multiple joins! Many thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/16/2013 :  00:43:53  Show Profile  Reply with Quote
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000