Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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...
0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
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
 
PortletPaulCommented:
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
 
lrbristerAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now