Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

COUNT OR SUM WITH CONDITION

Posted on 2012-09-19
2
Medium Priority
?
361 Views
Last Modified: 2012-09-19
Hi All,

I try below code :

SELECT
SecCode
, SUM(CASE WHEN ISNULL(MemberOf, '') <> '' THEN 1 ELSE 0 END) AS Member
, SUM(CASE WHEN ISNULL(MemberOf, '') = '' THEN 0 ELSE 1 END) AS NonMember
FROM TMEMPLOYEE
WHERE ResignDate Is NULL
GROUP BY SecCode
ORDER BY SecCode

Both Member and NonMember have same number result, this is wrong query.

What should I do  ?

Thank you.
0
Comment
Question by:emi_sastra
2 Comments
 
LVL 13

Accepted Solution

by:
LIONKING earned 2000 total points
ID: 38414516
Try this:

SELECT
SecCode
, SUM(CASE WHEN ISNULL(MemberOf, '') != '' THEN 1 ELSE 0 END) AS Member
, SUM(CASE WHEN ISNULL(MemberOf, '') = '' THEN 1 ELSE 0 END) AS NonMember
FROM TMEMPLOYEE
WHERE ResignDate Is NULL
GROUP BY SecCode
ORDER BY SecCode
0
 
LVL 1

Author Closing Comment

by:emi_sastra
ID: 38415506
Great.

Thank you very much for your help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 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