Solved

SQL Select

Posted on 2013-05-20
4
398 Views
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)

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

Expert Comment

by:Jim Horn
ID: 39181874
SELECT state, count(state) as cnt
FROM NURSES
WHERE (state = @state OR @state IS NULL)
GROUP BY state
ORDER BY state
0
 
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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39183288
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
 

Author Closing Comment

by:lrbrister
ID: 39185546
Thanks
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

747 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

10 Experts available now in Live!

Get 1:1 Help Now