Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Select

Posted on 2013-05-20
4
Medium Priority
?
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

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 66

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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

664 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