CODEWITHSUNDEEP
mysqlselectcount
Ben Dauphinee
Ben Dauphinee

Reputation: 4181

Is there a way to add conditions into a COUNT()?

What I want to do is add a COUNT into a query that is joined on several tables, but I only want to count the thing if it meets a specific condition.

I want the count to only count the rows that have a ReceivedDate like so: 

COUNT(ReceivedDate = "0000-00-00 00:00:00", plpq.PurchaseOrderID) AS OrdersForPart

Is there any way to achieve this without having to resort to a sub-query?

Upvotes: 2

Views: 90

Answers (3)

Martin Smith
Martin Smith

Reputation: 453298

Assuming you might want to perform some aggregation on the other rows and thus can't just exclude them entirely in the WHERE clause.

COUNT(CASE 
        WHEN ReceivedDate = "0000-00-00 00:00:00" 
        THEN plpq.PurchaseOrderID 
      END) AS OrdersForPart

If this isn't the case the WHERE clause is of course the best option.

Upvotes: 2

goto-bus-stop
goto-bus-stop

Reputation: 11824

Just use where.

SELECT COUNT(plpq.PurchaseOrderID) AS OrdersForPart FROM ... WHERE ReceivedDate = '000-00-00 00:00:00'

Should work just fine ;)

Upvotes: 2

Sean Owen
Sean Owen

Reputation: 66886

SELECT COUNT(plpq.PurchaseOrderID) 
  AS OrdersForPart 
  FROM table 
  WHERE ReceivedDate = "0000-00-00 00:00:00"

Upvotes: 1

Related Questions