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
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
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.
IF you already have it in one of the tables you should be able to select them from there and group on that
