Solved

MS SQL 2008 Count Non 0 rows

Posted on 2011-02-15
3
396 Views
Last Modified: 2012-05-11
Hi,

I have a table with a int field.  Without using a sub query I'd like to count the number of rows based on a condition, i.e. count the number of rows where a column is not 0.  i.e.
SELECT *, (COUNT(* WHERE COL A IS NOT 0)) FROM tablename.
0
Comment
Question by:kinton
3 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 34896746
You coudl just do a SUM with a case statement.

SELECT SUM (WHEN COL <> 0 THEN 1 ELSE 0 END )
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34896759
> Without using a sub query
you cannot do that at the same level as the query itself ...
0
 
LVL 2

Author Closing Comment

by:kinton
ID: 34897022
Great, good answer!  Should have thought of that..
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

680 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