SQL Select

Posted on 2013-05-20
Last Modified: 2013-05-21
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)


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
Question by:lrbrister
  • 2
LVL 65

Expert Comment

by:Jim Horn
ID: 39181874
SELECT state, count(state) as cnt
WHERE (state = @state OR @state IS NULL)
GROUP BY state
ORDER BY state
LVL 65

Expert Comment

by:Jim Horn
ID: 39181890
>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.
LVL 48

Accepted Solution

PortletPaul earned 500 total points
ID: 39183288

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 count(*) from nurses) as ttl
, state
, count(state) as cnt
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

Author Closing Comment

ID: 39185546

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now