How to count total id before this id?

Question

i want to count total data id before my id. example now i sent my id is 7 i want to count id with status=4 before id 7, but if status!=4 dont count again . how to fix it?

id | status|
------------    
 1 |   4   |
 2 |   1   |
 3 |   2   |
 4 |   2   |
 5 |   4   |---> GET THIS DATA
 6 |   4   |---> GET THIS DATA
 7 |   0   |--> MY ID
 8 |   0   |
 9 |   0   |

This is my code:

SELECT (SELECT COUNT(id) FROM `table_cicilan` WHERE `status` = '4' AND id < '7') AS `position` FROM `table_cicilan` WHERE `status` = '4' AND id='7' AND kode_cicilan='CICIL1'

thanks

Possible duplicate of [MySQL get row position in ORDER BY](https://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by) — misorude, Aug 07 '19 at 14:14
Is PHP related to the question? Seems to just be a SQL/mysql question. maybe `select count(*) from table where status = (select status from table where id < ? order by id desc limit 1)` ... or why is id 1 not counted? ` i want to count id with status=4 before id 7` would include 1. — user3783243, Aug 07 '19 at 14:17
@KIKOSoftware Oh yea that subquery should have a `limit 1` as well. — user3783243, Aug 07 '19 at 14:21
*"Oh yea that subquery should have a limit 1 as well"* you mean limit 2 @user3783243, topicstarter wants two records.. — Raymond Nijland, Aug 07 '19 at 14:21
OK, I'll answer on behalf of MarkOverton: It doesn't matter if your code works or not. If it did you wouldn't be asking this question. We need the code to see what's gone wrong, so we can actually help you. Otherwise we would have to give you a ready made solution, which might not fit in with what you've got. — KIKO Software, Aug 07 '19 at 14:22
@KIKOSoftware No, limit 1 because OP wants the rows with the same status below the closest record. `with status=4 before id 7` — user3783243, Aug 07 '19 at 14:23
```SELECT (SELECT COUNT(id) FROM `table_cicilan` WHERE `status` = '4' AND id < '7') AS `position` FROM `table_cicilan` WHERE `status` = '4' AND id='7' AND kode_cicilan='CICIL1'``` This is my code — Gilang Permana, Aug 07 '19 at 14:23
*"No, limit 1 because OP wants the rows with the same status below the closest record."* if thats the case @user3783243 then his question description and ascii table descriptions `---> GET THIS DATA` are contradictory — Raymond Nijland, Aug 07 '19 at 14:25
'4' because '4' is the status of the row immediately before id 7 or because '4' is a user-supplied variable? — Strawberry, Aug 07 '19 at 14:25
i don't understand the question and expected results pretty sure more poeple also do not understand this question while reading this question and comments.. Topcistarter See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Aug 07 '19 at 14:26
@RaymondNijland Yes, unclear about the question really. `i want to count total data` sounds like OP only wants a count, but that table looks like the specific rows are required. Additionally it is unclear what count should be returned because the description would include row 1. — user3783243, Aug 07 '19 at 14:27

spencer7593 · Answer 1 · 2019-08-07T15:07:25.190

1

Specification isn't entirely clear. But given "MY ID" value of 7, and looking for rows immediately preceding (by id value) that have a status of 4, and we are looking to return a count of those rows, then we could do something like this:

SELECT COUNT(*) AS cnt
  FROM t
 WHERE t.id  <  7                                         /* <-- MY ID -- */
   AND t.id  > IFNULL( ( SELECT b.id
                           FROM t b
                          WHERE NOT ( b.status <=> 4 )
                            AND b.id  <  7                /* <-- MY ID -- */
                          ORDER BY b.id DESC
                          LIMIT 1
                        )
              ,POWER(-2,63)
              )

The query is making accommodations for possibilities that id is big integer that allows for negative values, and for NULL values of status. The query could be simplified a bit if we had knowledge that id is UNSIGNED, or status is non-NULL.

The query in this answer satisfies one possible interpretation of the specification. It's also possible to modify the SELECT list to return the actual rows, rather than a COUNT(), or return the minimum id value, etc.

Note: If the status value of "4" isn't a user-supplied value, and what we want is the value of the status column on the immediately preceding row, we could change the query to do that.

But given the question specifies an explicit value of "4", and there is no mention of checking the immediately preceding row to get the value of the status column on that row, it seems a bit premature to demonstrate SQL to do something other than the specified value of "4".

edited Aug 07 '19 at 15:07

answered Aug 07 '19 at 14:35

spencer7593

106,611
15
112
140

@spencer7593 why return POWER(-2,63) if subquery expression is null? – Sayed Mohd Ali Aug 08 '19 at 06:09
@SayedMohdAli: great question. if `id` is unsigned integer, then we could just return -1, or 0, Consider the case when *all* of the rows before id 7 are status 4, `(1,4),(2,4),(3,4),(4,4),(5,4),(6,4),(7,0),..` according to the spec, we want to return a count of 6. The subquery that attempts to get the preceding row with status other than 4 is going to not find a row, and return NULL. The condition is checking for rows *after* the non-4-status row, when we get NULL we can specify a value lower that the lowest possible `id` value, which for BIGINT datatype is `-2^63`. – spencer7593 Aug 08 '19 at 14:05
okay, I have upvoted your answer... and understood the logic... but in that case, we can also just return the total count... if all the values before 7 is 4 and we unable to find the non 4 status value before 7 ohh but how will you find it that all the rows before 7 are 4.... – Sayed Mohd Ali Aug 08 '19 at 14:09
@SayedMohdAli: right, if the subquery returns a NULL, we want to drop that condition from the WHERE clause. so maybe we can do an OR condition, and run that subquery again to see if it's NULL, that's ugly. maybe we couldf ix the subquery. But how do we get it to return an id value lower than any id that exists in the table? the quick fix is to just test for NULL and replace it with a zero. oh, but what if `id` can be zero. we can return -1. ohh, but what if `id` can be negative, and what if id is BIGINT, that's how I arrived at `IFNULL( ... ,POWER(-2,63))` (granted its a corner case) – spencer7593 Aug 08 '19 at 14:20
and in the most extreme corner case where we have status=4 all the way back to the lowest possible `id` value of `POWER(-2,63)`, i'm okay with the count being "one row off", missing the very first row, because of the `>` comparison. – spencer7593 Aug 08 '19 at 14:27

Sayed Mohd Ali · Answer 2 · 2019-08-09T14:21:56.727

Here is the most simple solution... there is no need to check for the NULL values because OP didn't specify... if you want to check null values then use spencer code

 SELECT COUNT(*) AS total_count
      FROM table_cicilan
     WHERE table_cicilan.id  >  IFNULL(( SELECT tc.id
                               FROM table_cicilan tc
                              WHERE tc.status != 4 
                                AND tc.id  <  7               
                              ORDER BY tc.id DESC
                              LIMIT 1
                            ),-1)
           AND   table_cicilan.id  <  7

Here is the explanation of the code...

what we are doing here is simply selecting rows between is less than 7 up to the rows whose status is not equal to 4 and counting such rows... so you get the logic that means you can also do with between operator? yes you can do that but you need to remove the upper limit and lower limit rows count from the table so I am subtracting the 2 values but this is not the right way to do it here is you need to check for the count > 0 because suppose no record is found then the count will be -2 because of substraction.

SELECT IF((COUNT(*)-2) >0,(COUNT(*)-2),0) AS total_count
  FROM table_cicilan
 WHERE table_cicilan.id  between  IFNULL(( SELECT tc.id
                           FROM table_cicilan tc
                          WHERE tc.status != 4 
                            AND tc.id  <  7               
                          ORDER BY tc.id DESC
                          LIMIT 1
                        ),-1)
       AND 7

also consider the edge case when *every* row with `id<7` has `status=4`. (That subquery isn't going to return a row, and will evaluate to NULL. And we know an inequality comparison to a NULL value will never evaluate to true. Consider set `(1,4),(2,4),(3,4),(4,4),(5,4),(6,4),(7,0)`.) And an entirely different edge case, we don't see a guarantee that `status` will be non-NULL. So what should be the return for set like this: `(1,4),(2,4),(3,NULL),(4,NULL),(5,4),(6,4),(7,0)`. Should we return a count of 2, or a count of 6? — spencer7593, Aug 08 '19 at 14:15
@spencer7593 checking null and returning -1 also solve the problem... if Ids are positive integers is this good solution or bad? — Sayed Mohd Ali, Aug 09 '19 at 06:25
yes, that seems like it is sufficient to handle the "all preceding rows are status=4" edge case. — spencer7593, Aug 09 '19 at 14:01
@spencer7593 yes, I tried to solve the NULL case scenario with an alternative query I have given the between option... it is giving wrong output in both the cases if I try -1 or power(-2,63)... — Sayed Mohd Ali, Aug 09 '19 at 14:26

How to count total id before this id?

2 Answers2