SQL Query having where clause in count

Hi,

I am trying to get count on a column with a condition.Any suggestion on how to use count having a where clause
example;
 WHERE (C.RegistrationNumber is null or C.RegistrationNumber =  '') and count the return value

Cheers
RIASAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try...

select c.yourfield,
sum(case when C.RegistrationNumber is null or C.RegistrationNumber =  '' then 1 else 0 end) yourCnt
from yourTable C
Group by c.yourfield

Open in new window

RIASAuthor Commented:
This query just returns 1,0 ,need to have sum of the returned records.

The query is
Return employees who have more than 1 car  
Include a column that gives the number of cars they have registered


Cheers
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
how's your data looks like and what's your expected output?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

select c.yourfield,
count(C.RegistrationNumber) yourCnt
from yourTable C
where C.RegistrationNumber is not null and C.RegistrationNumber <> ''
Group by c.yourfield

Open in new window

RIASAuthor Commented:
Cheers mate wiil try and get back !
RIASAuthor Commented:
This query is errored

select E.EmployeeID,E.Firstname
count(C.RegistrationNumber) Number_of_Cars_Registered FROM
B35_Employees E  INNER JOIN B35_Cars C
       ON E.EmployeeID = C.EmployeeID
WHERE COALESCE(C.RegistrationNumber, '') <> ''
Group by C.EmployeeID
Jim HornMicrosoft SQL Server Data DudeCommented:
Filtering a query (i.e. WHERE) on an aggregate such as SUM or COUNT is the HAVING clause.

SELECT E.EmployeeID, E.Firstname, count(C.RegistrationNumber) Number_of_Cars_Registered
FROM B35_Employees E  
   INNER JOIN B35_Cars C  ON E.EmployeeID = C.EmployeeID
WHERE COALESCE(C.RegistrationNumber, '') <> ''
GROUP BY C.EmployeeID, E.Firstname
-- Looky here, Return employees who have more than 1 car record
HAVING count(C.RegistrationNumber) > 1


Some bonus reading for you would be my article SQL Server GROUP BY Solutions
Jim HornMicrosoft SQL Server Data DudeCommented:
For example..

Select athlete, year, Sum(annual salary)
FROM league_stats
GROUP BY athlete, year
HAVING Sum(AnnualSalary) > 1000000   -- that made over a million bucks in their careers
Mark WillsTopic AdvisorCommented:
Well, if including columns to help show row selection (such as ID and name) then they have to be included in the group by as well...

OR

Use window functions (ie the OVER() clause)

e.g.

-- group by
select E.EmployeeID,E.Firstname ,
       count( C.RegistrationNumber)  as Number_of_Cars_Registered 
 FROM  B35_Employees E 
 INNER JOIN B35_Cars C ON E.EmployeeID = C.EmployeeID
 WHERE COALESCE(C.RegistrationNumber, '') <> ''
GROUP BY E.EmployeeID,E.Firstname

-- window function
select E.EmployeeID,E.Firstname ,
       count( C.RegistrationNumber) over (partition by E.EmployeeID,E.Firstname) as Number_of_Cars_Registered 
 FROM  B35_Employees E 
 INNER JOIN B35_Cars C ON E.EmployeeID = C.EmployeeID
 WHERE COALESCE(C.RegistrationNumber, '') <> ''
 
 -- the above will generate more rows (ie same count, but repeated for each additional rego)
-- or add in DISTINCT after the select
-- or use the group by version

Open in new window



So, the only thing wrong (as far as syntax goes) in your query that errored was the GROUP BY not reflecting all the non-aggregated columns mentioned in the SELECT

ie from "This query is errored" because you are doing a " select E.EmployeeID,E.Firstname"
then you must group by those same columns and not Group by C.EmployeeID which is really just the target of the aggregate

Does that make sense (it can be confusing) ?
PortletPaulEE Topic AdvisorCommented:
>>"This query is errored"

1. There is a comma missing in the select clause
2. ALL "non-aggregating" columns have to be in the GROUP BY

This is your query, just re-formatted
SELECT
      E.EmployeeID
    , E.Firstname
      count (C.RegistrationNumber) Number_of_Cars_Registered
FROM B35_Employees E
      INNER JOIN B35_Cars C ON E.EmployeeID = C.EmployeeID
WHERE COALESCE(C.RegistrationNumber, '') <> ''
GROUP BY
      C.EmployeeID

Open in new window

Here it is with the corrections:
SELECT
      E.EmployeeID
    , E.Firstname
    , COUNT(C.RegistrationNumber) Number_of_Cars_Registered
FROM B35_Employees E
      INNER JOIN B35_Cars C ON E.EmployeeID = C.EmployeeID
WHERE COALESCE(C.RegistrationNumber, '') <> ''
GROUP BY
      E.EmployeeID
    , E.Firstname

Open in new window

Note that basically this means you copy every column except those using SUM() COUNT() AVG() etc., from the select list and put them into the group by list.

As a general rule it is much better for performance to "avoid using functions in the where clause" so that COALESCE() should be avoided if possible. I suggest this:
SELECT
      E.EmployeeID
    , E.Firstname
    , COUNT(C.RegistrationNumber) Number_of_Cars_Registered
FROM B35_Employees E
      INNER JOIN B35_Cars C ON E.EmployeeID = C.EmployeeID
WHERE C.RegistrationNumber IS NOT NULL
      AND C.RegistrationNumber <> ''
GROUP BY
      E.EmployeeID
    , E.Firstname

Open in new window

The query optimizer can make use of an index on C.RegistrationNumber now if we avoid using that COALESCE()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RIASAuthor Commented:
Cheers Experts!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.