MYSQL Row Count if criteria is met

Question

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

You might want to look at http://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql — Brian Hoover, Feb 15 '14 at 15:56

score 1 · Answer 1 · answered Feb 15 '14 at 15:59

1

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

answered Feb 15 '14 at 15:59

Ian Kenney

6,376
1
25
44

echo_Me · Answer 2 · 2014-02-15T16:19:56.480

0

    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

edited Feb 15 '14 at 16:19

answered Feb 15 '14 at 15:55

echo_Me

37,078
5
58
78

Yeah- that's DEFINITELY not what was wanted ! :-) – Strawberry Feb 15 '14 at 16:08

score 0 · Answer 3 · answered Feb 15 '14 at 16:18

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

score 0 · Answer 4 · answered Feb 15 '14 at 16:19

0

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

answered Feb 15 '14 at 16:19

Rafa Paez

4,820
18
35

Sorry for the delay in responding. This looks good but I was hoping to show the value of the 'passed' column to show an incremental value. For example, if I had 4 rows that matched isHome = 1, then each passed column in each row would not show 4 but would show 1,2,3,4. Not sure if that makes any sense – user3313704 Feb 20 '14 at 00:42

MYSQL Row Count if criteria is met

4 Answers4