How to Create this table with specific heirarchy

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

How to Create this table with specific heirarchy

Author

Topic

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-20 : 00:02:30

Hi i need to create this query. I have a table here named "Subject" from the database "Filteredtask". I want the output to show in this way:

Subject Quantity

Application Recieved count of application recieved
Process Application count of process application
Screen Application count of screen application
Initial Interview count of initial interview
Technical Exam and Interview count of technical exam and interview

I have used this code. My problem is that i put it in descending order to obtain this output, but if for example Screen application had more count it will ruin the hierarchy of the subject.

SELECT subject, COUNT(subject) AS Expr1
FROM dbo.FilteredTask
WHERE (subject = N'application received') OR
(subject = 'process application') OR
(subject = 'screen application') OR
(subject = N'initial interview') OR
(subject = 'profiles assessment') OR
(subject = 'technical exam and interview')
GROUP BY subject
ORDER BY expr1 DESC

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 00:58:22

[code]SELECT Subject,
MIN(CASE Subject
WHEN N'Application Received' THEN 1
WHEN 'Process Application' THEN 2
WHEN 'Acreen Application' THEN 3
WHEN N'Initial Interview' THEN 4
WHEN 'Profiles Assessment' THEN 5
WHEN 'Technical Exam and Interview' THEN 6
ELSE 0
END) AS RowOrder,
COUNT(*)
FROM dbo.FilteredTask.Subject
GROUP BY Subject
ORDER BY 2[/code]

Peter Larsson
Helsingborg, Sweden

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-20 : 02:37:55

Thanks peso.. By the way I forgot to tell you that there are other data in the table exept to the one I have specified. So if I run the syntax it shows all of the items. May you please help me output only the six items below?

Thanks again

Regards,

Andrew

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 03:25:48

1) Remove the ELSE line
2) Add a WHERE clause
WHERE Subject IN ('Application Received', 'Process Application', 'Acreen Application', 'Initial Interview', 'Profiles Assessment', 'Technical Exam and Interview')

Peter Larsson
Helsingborg, Sweden

drewgeezmoe
Starting Member

22 Posts

Posted - 2007-02-20 : 03:52:18

Thanks a lot...

Regards,

Andrew

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources