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

x
Solved

# help with SQL conditional report

Posted on 2014-02-28
Medium Priority
377 Views
i have a table (stacked) with variables cipcode, awlevel, unitid, and year.

im trying to generate a report that will tell me the distinct count of unitid where awlevel=5 and cipcode=45.0601 in 2001 but not 2011.  Basically i need results where cipcode only exists when year=2001 and doesnt exist when year=2011.

so far i have this, which gives me the results for 2001 only
``````	select count(distinct(unitid)) as Grad11 label="Schools with Graduations",
from stacked
where awlevel=5 and cipcode='45.0601' and year=2001;
``````

I think the answer involves nested selects but i cant wrap my head around it.
0
Question by:jmichael18
[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

LVL 34

Accepted Solution

Brian Crowe earned 2000 total points
ID: 39896757
DECLARE @cipcode FLOAT,
@awlevel INT;

SELECT @cipcode = 45.0201,
@awlevel = 5

SELECT COUNT (DISTINCT unitid) AS Grad1
FROM stacked AS s2001
LEFT OUTER JOIN stacked AS s2011
ON s2001.cipcode = s2011.cipcode
WHERE s2001.cipcode = @cipcode
AND s2001.awlevel = 5
AND s2011.cipcode IS NULL
0

Author Closing Comment

ID: 39912835
Thanks!
0

## Featured Post

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
###### Suggested Courses
Course of the Month7 days, 4 hours left to enroll

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

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