CODEWITHSUNDEEP
mysqlsql
Jordash
Jordash

Reputation: 3103

Count based on inner select

I want to do a count based on a sub select statement.

SELECT MONTH(Received), State, COUNT('id'), SUM(price) FROM Leads GROUP BY MONTH(Received), State

That query works and generates something like this

MONTH(Received)     State   COUNT('id')    SUM(price)
4                   CA      25             35
4                   UT      13             8

What I'm trying to count is how many rows had a price > 0

This is the query I tried

SELECT MONTH(Received), SUM(price), COUNT(`id`), (SELECT COUNT(*) as count FROM Leads WHERE Price > 0) as NumPaidLeads, State FROM Leads GROUP BY MONTH(Received), State

But NumPaidLeads Counts the entire column, the price column is numeric type.

What am I doing wrong?

Upvotes: 1

Views: 35

Answers (2)

Daniel
Daniel

Reputation: 426

You forgot to include state in the where-clause of your sub-select. The case-solution in the other post may be faster, though.

SELECT MONTH(Received), SUM(price), COUNT(`id`), 
 (SELECT COUNT(*) as count FROM Leads as L1 WHERE Price > 0 
  and L1.State=L2.State) as NumPaidLeads,
 State FROM Leads as L2 GROUP BY MONTH(Received), State

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

in your case for count the rows with price > 0 you could use a case

SELECT MONTH(Received), State, 
 sum( case when price > 0 then 1 else 0 END) num_row_with_price, SUM(price) 
FROM Leads 
GROUP BY MONTH(Received), State

Upvotes: 1

Related Questions