CODEWITHSUNDEEP
mysql
user3313704
user3313704

Reputation: 11

MYSQL Row Count if criteria is met

Sorry but I am not sure how to phrase this question or if it is possible but basically I am using a select statement in which I would like to display a column showing a count of when a criteria is met. For example

SELECT pageID, isHome, if(ishome = 1, 'count?', 0) AS Passed
FROM pages

I would like the passed column to show a running count kind of like this 

PageID | ishome | passed
10031  | 0      | 0
10032  | 1      | 1
10033  | 1      | 2
10034  | 1      | 3

Thank you for any help

Upvotes: 1

Views: 70

Answers (4)

Rafa Paez
Rafa Paez

Reputation: 4860

Try something like this

SELECT pageID, isHome,
CASE isHome 
  WHEN 1 THEN SELECT COUNT(*) FROM pages p 
              WHERE p.isHome = 1 AND p.pageID <= pageID 
  ELSE 0 
END as passed
FROM pages
ORDER BY pageID ASC

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

    SELECT pageID, isHome,  count(*)  AS Passed 
    FROM pages
    where ishome = 1
    GROUP BY PageID

DEMO

if you want show all result then use this

 SELECT pageID, isHome,  if(ishome = 1, count(*), 0)  AS Passed 
 FROM pages
 group by pageID,ishome ;

DEMO

Upvotes: 0

G one
G one

Reputation: 2729

Not sure but I think this is want you want.

SELECT pageID, isHome, 
case when(ishome = 1)
  then count(*)
  else 0
end Passed FROM Table1
group by pageID;

sqlfiddle

OR if you want the number of passed pages

SELECT isHome, 
case when(ishome = 1)
  then count(*)
  else 0
end Passed FROM Table1
group by isHome;

sqlfiddle

Upvotes: 0

Ian Kenney
Ian Kenney

Reputation: 6426

Try something like the following (untested)

select a.pageid, a.ishome, sum(b.ishome) passed 
from 
pages a join pages b
on a.pageid>= b.pageid
group by a.pageid, a.ishome
order by a.pageid

Upvotes: 1

Related Questions