Query with percentages based on group

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query with percentages based on group

Author

Topic

BizCraig
Starting Member

5 Posts

Posted - 2011-10-06 : 10:55:53

Spent a while trying to figure this out maybe you guys can help,

I have a database of employees and a database of skill evaluations(1perEmployee) From this I am trying to build a report of the percentage of people who have skill evaluations stored against them. In each group(a place where they work) these also have higher up groups of the area(Town) and region(County).

But have next to no idea how to this in!

Also to further complecate thing only employees who have not left the buisness should shown(a Field set to yes/no) so all data has to be queried with this parameter.

Hopefully you can help out and set me on the right path with this,
Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:20:40

you need to apply group by on relevant fields Town,County etc and to get percentage apply formula like COUNT(skillevaluation)*100.0/COUNT(employees)
for including employees who are in business use a filter field='Yes'

these are only things i can suggest with limited information provided.
Post table structure,sample data and reqd output if you want more accurate suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BizCraig
Starting Member

5 Posts

Posted - 2011-10-07 : 08:54:12

Hi,
A simple version of my tables would be


Employee:
  ID 
 Name 
 Left(Y/N) 
  Support(where they work)


Supports:
  Support 
 Area 
  Region 


Evaluation:
  Employee  
 Q1 
 Q2 
 ect...

______________________________

The ideal output would be something like this


Yorkshire           x%
   Sheffield        x% 
     xxxx street    x%
     yyyy street    x%
   Leeds            x%
     zzzz street    x%
     aaaa street    x%
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 13:07:21

is this for reporting need? if yes, the format can achieved using reporting tools using grouping levels.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BizCraig
Starting Member

5 Posts

Posted - 2011-10-10 : 03:46:18

Yes this is for reporting, and the issue that i cant understand is how to use count() on different sets of data. As the value would be the:
(count of employees in the group)/(count of employees in the group where there is no entry for the skillsanalysis)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 04:31:09

apply grouping in report container row and in expression give like
=Count(Fields!EmployeeName.value)
for getting employee count
for getting count of employees in the group where there is no entry for the skillsanalysis use like

=Count(IIF(Fileds!SkillField.value Is Nothing,Fields!EmployeeName.value,Nothing))

I've assumed that you're using SQL reporting services
ALso field names are assumed ones, please replace them with actual filenames.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BizCraig
Starting Member

5 Posts

Posted - 2011-10-10 : 06:38:13

Ah so this is using a calculated field of sorts, in the report? I've been trying to this via the sql query itself

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-10 : 06:49:33

even in sql you can do it like


SELECT COUNT(EMployeeName),
COUNT(CASE WHEN SkillField IS NULL THEN EmployeeName ELSE NULL END)
FROM...
GROUP BY GroupingFieldName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

BizCraig
Starting Member

5 Posts

Posted - 2011-10-11 : 10:21:50

Thanks alot implemented the expression based solution you suggested and it worked a treat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources