SQL Subqueries - SQL Server Forums

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

Database Design and Application Architecture

SQL Subqueries

Author

Topic

lokeshsu
Starting Member

1 Post

Posted - 2010-07-09 : 12:04:01

Hi All,

Need som help in subqueries in sql. i have a table of data where we have 3 fields empno name status. in status we have 5 different status and each empno will have all the 5 status. i need a query which will count the unique numbers in the table and if the status is in the last stage it should not show the count in previous stage.in the report so need help in this query

Regards,
Lokesh Sundar

sec
Starting Member

4 Posts

Posted - 2010-07-21 : 14:21:42

1> Use a subselect to return a table of empnos with the maximum status (the status name will have to be hierachical so that the 'maximum' can be identified; maybe add statlevel)

2> Get a count of these records

Note: An alias for the subselected table and columns are required

SELECT tbl.maxstat, COUNT(*) FROM (
SELECT emp.empno, MAX(emp.status) AS maxstat FROM tblEmpInfo emp GROUP BY emp.empno
) tbl
GROUP BY tbl.maxstat

Hope this helps give you a direction

sec

Let God be true and every man a liar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources