question

Yasemin avatar image
Yasemin asked

Grouping values and counting for each distinct department

Hi, I have a query that counts number of offices per department which returns values in this format but the actual list is much longer: Office HR Corporate Bahrain 2 1 Beijing 2 8 Berlin 1 3 Brussels 7 5 Cologne 3 1 Dubai 6 6 Düsseldorf 2 3 Hong Kong 6 1 London 33 14 Instead of each office, I want to group the Offices into Regions and return values per region: e.g. where office is Berlin, Cologne, Dusseldorf > the region would be Germany, where Bahrain, Dubai > the region is MENA and the query should return number of depaertments HR , Corporate, Finance etc.. per region. I got the grouping of offices into regions working e.g. (SELECT COUNT(distinct a.entryid) FROM Entry as a inner join entrycategory as b on b.entryid = a.entryid inner join EntryAttribute AS X on X.entryID = A.entryid inner join attributestring as y on y.attributeid= x.attributeid WHERE y.attributedefinitionid=42 and exists (select * from EntryAttribute AS y inner join attributedatetime as z on z.attributeid=y.attributeid WHERE z.attributedefinitionid=17 and y.entryID = A.entryid and z.attributevalue between '2010-05-01' and '2010-07-31') and y.attributevalue in ('Beijing','Shanghai','Hong Kong','Hanoi','Ho Chi Minh City','Tokyo'))as ASIA However, I can't seem to add the department data to it. If I join these tables below for department and add deptname to the select query above, it returns an error message stating that the deptname is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Select g.deptname 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 [DEPT] as g on g.groupID = c.groupID Can you please help. Many thanks. Yasemin
sql-server-2005group-byrow-counts
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

don't ya just love EAV!
2 Likes 2 ·

1 Answer

· Write an Answer
Håkan Winther avatar image
Håkan Winther answered
Have you tried to add : GROUP BY g.deptname I also want to recommend you to use a tempory table instead of your IN clause to improve the performance: CREATE TABLE #attributevalue ( val varchar(50), region varchar(50) ) INSERT INTO #attributevalue VALUES ('Beijing', 'Asia'), ('Shanghai', 'Asia'), ('Hong Kong', 'Asia'), ('Hanoi', 'Asia'), ('Ho Chi Minh City', 'Asia'), ('Tokyo', 'Asia') ('Berlin', 'Europe') ('Stockholm', 'Europe') SELECT av.region, g.deptname, COUNT(distinct a.entryid) 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 [DEPT] as g on g.groupID = c.groupID inner join EntryAttribute AS X on X.entryID = A.entryid inner join attributestring as y on y.attributeid= x.attributeid inner join #attributevalue as av on y.attributevalue = av.val WHERE y.attributedefinitionid=42 and exists (select * from EntryAttribute AS y inner join attributedatetime as z on z.attributeid=y.attributeid WHERE z.attributedefinitionid=17 and y.entryID = A.entryid and z.attributevalue between '2010-05-01' and '2010-07-31') GROUP BY g.deptname, av.region
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have tried Group by but it only works for one region at a time. My query selects all offices and groups them into regions, I tried to paste it in here but it's too long for this comments box. Can I attach a document here?
0 Likes 0 ·
Ok, I changed my temptable and select/group by to include region and now this will probably work for you as long as you can fill the temptable with cities and regions.
0 Likes 0 ·
Sorry if I am being silly but the list of Offices already exist in the AttributeString table in the column named Attributevalue. So why am I inserting them again in a temp table? Should I not select from the existing table and group them accordingly?
0 Likes 0 ·
IF you already have it in one of the tables you should be able to select them from there and group on that
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.