Count(*) in SQL

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

What does count(1) in SQL mean?

Basically, count(1) produces just the same result as count(*): that is, it counts the number of records in the group defined by the group by clause.

Why? count(<expr>) counts every non-null value of <expr>. Here it is given a constant value, 1, that is never null - so it counts all rows.

I wouldn't recommend count(1); checking for nullity of each and every row requires more work for the database that just counting all rows. Some databases might optimize count(1) as count(*) under the hood (as it is obvious that this fixed expression will never be null), but why bother, when you can optimize yourself already? Just use count(*) consistently.

Count(*) vs Count(1) - SQL Server

There is no difference.

Reason:

Books on-line says "COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )"

"1" is a non-null expression: so it's the same as COUNT(*).
The optimizer recognizes it for what it is: trivial.

The same as EXISTS (SELECT * ... or EXISTS (SELECT 1 ...

Example:

SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1),FKID FROM dbo.tab800krows GROUP BY FKID

SELECT COUNT(*) FROM dbo.tab800krows
SELECT COUNT(*),FKID FROM dbo.tab800krows GROUP BY FKID

Same IO, same plan, the works

Edit, Aug 2011

COUNT(*) in SQL

It's an aggregate function - as such it's managed by your group by clause - each row will correspond to a unique grouping (i.e. staffNo) and Count(*) will return the number of records in the join that match that grouping.

So for example:

 SELECT branch, grade, Count(*)
 FROM Staff s, Properties p 
 WHERE s.staffNo = p.staffNo
 GROUP BY branch, grade

would return the number of staff members of a given grade at each branch.

 SELECT branch, Count(*)
 FROM Staff s, Properties p 
 WHERE s.staffNo = p.staffNo
 GROUP BY branch

would return the total number of staff members at each branch

 SELECT grade, Count(*)
 FROM Staff s, Properties p 
 WHERE s.staffNo = p.staffNo
 GROUP BY grade

would return the total number of staff at each grade

How to re-name and reference COUNT(*) in a SELECT statement?

You can alias a column by simply putting a name after it, optionally with the keyword AS in between. It's essentially the same as you already do with the tables.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) AS numliasons
       FROM school s;

or simply

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s;

But you cannot use aliases in the WHERE clause (aliasing is happening after the records have been selected by the criteria in the WHERE clause). You have to repeat the expession.

SELECT school_name,
       (SELECT count(*)
               FROM liason_to l
               WHERE l.school_name = s.school_name) numliasons
       FROM school s
       WHERE (SELECT count(*)
                     FROM liason_to l
                     WHERE l.school_name = s.school_name) > 0;

SQL count (*) function query for a baseball data table explanation

In your code the aggregation functions

  , sum(rbi) as rbis
  ,  count(*) as num_players

agregated by column First

count(*) retunr the number of rows for the corresponding groped value

so in your case retunr the number of rows in your table for the corresponding value of olumn First

SQL COUNT(*) Confusion

This is how I would parse your query:

SELECT   VendorState, VendorCity, COUNT(*) AS 'Invoice QTY',
         AVG(InvoiceTotal) AS 'InvoiceAvg'

Okay, you select some blah blah. I'd come back here after parsing to see whether the chosen columns are really available, but here you have no errors so I'll assume the columns are good.

FROM     Invoices

You start with all the invoices (perhaps this is the point that confuses you).

JOIN Vendors
ON       Invoices.VendorID = Vendors.VendorID

Each Invoice is joined to a single Vendor (VendorID is a primary key), so the cardinality does not change (assuming all vendors are still in place of course; an invoice with no matching VendorId will "disappear". Usually this is not the case when invoices and vendors are involved; you might have a flag to exclude "terminated" vendors, but you wouldn't remove invoices from the database). The important thing is, if you had 1,000 invoices, you now have 1,000 rows after the JOIN, not 2,000 or any other number. So, you're still working with invoices.

GROUP BY VendorState, VendorCity

Okay, so the COUNT(*) refers to the invoices of each single city in each single state. The HAVING clause restricts the results to those cities where at least two invoices are present.

In SQL, what's the difference between count(column) and count(*)?

count(*) counts NULLs and count(column) does not

[edit] added this code so that people can run it

create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)

select count(*),count(id),count(id2)
from #bla

results
7 3 2

sql query to count number of -1's and 1's present in a column

You can GROUP BY column1 and use a CASE expression that returns 'positive' or 'negative':

SELECT CASE column1 
         WHEN 1 THEN 'positive'
         WHEN -1 THEN 'negative' 
       END sign, 
       COUNT(*) counter
FROM table1
GROUP BY column1

But if there is a case that there are no positives or no negatives it is better to use conditional aggregation with UNION ALL:

SELECT 'positive' sign, COUNT(CASE WHEN column1 = 1 THEN 1 END) counter FROM table1
UNION ALL
SELECT 'negative', COUNT(CASE WHEN column1 = -1 THEN 1 END) FROM table1

See a simplified demo.