SQL Select

I need to put two selectes together

1. Select @totalNurses as totalNurses

2. Select state, count(state) as cnt from nurses where state = ISNULL(@state,state)

So...

If @totalNurses  = 1000
and @state = 'KY'
Data would look like this

ttl     state    cnt
1000  KY       25

But if @state is null
ttl     state    cnt
1000  AK        5
1000  AL         9
1000  AR        17
etc...
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
mmmmm

does the nurses table indicate if a nurse is no longer practicing?
i.e. could your counts be misleading because you are not considering other fields of information

anyway, perhaps the 'first query' for total nurses could be handled like this:

SELECT
  (select count(*) from nurses) as ttl
, state
, count(state) as cnt
FROM NURSES
WHERE (state = @state OR @state IS NULL)
GROUP BY state
ORDER BY state

here: if parameter @state is null, then "cnt" and "ttl" would be the same value
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT state, count(state) as cnt
FROM NURSES
WHERE (state = @state OR @state IS NULL)
GROUP BY state
ORDER BY state
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>1. Select @totalNurses as totalNurses
I didn't see that anywhere in your SQL, so you'll have to spell out if this is anything other than a hard-coded value in your return recordset.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.