Click here to Skip to main content
15,310,880 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have below table

Level	Check	Count
0	     N	
1	     Y	     0
2	     Y	
1	     N	     0
1	     N	     0
1	     N	     5
2	     N	
2	     N	
2	     N	
2	     N	
2	     N

how to get the count of N in check column which is 5 for level 2 as shown in table and result has to populate in count column as 5

What I have tried:

I tried with count and lead but not getting correct output
Updated 18-Mar-21 1:17am
Maciej Los 18-Mar-21 7:14am
Show us what you have tried.

Probably something like:
SELECT COUNT(Level) FROM [table_name] WHERE Check='N';

See SQL COUNT(), AVG() and SUM() Functions[^]
Member 14936854 18-Mar-21 6:51am
Thank for the query But i need count at column level ends with 1 as shown in table for level=2
Richard MacCutchan 18-Mar-21 7:06am
Sorry, I do not understand what you are asking for. The COUNT() function is quite easy to use, you just need to select the appropriate column and the conditions in the WHERE clause.
Member 14936854 18-Mar-21 6:53am
in above table if you see count of 2 in level is 5 and which is populated to last value or end of level 1
Maciej Los 18-Mar-21 7:14am
There's nothing like "end of level 1"!
Maciej Los 18-Mar-21 7:17am
Richard MacCutchan 18-Mar-21 7:34am
Thanks, but my SQL knowledge is fairly basic.
Maciej Los 18-Mar-21 8:26am
I have to gentle disagree. Your knowledge is fair enough. :)
Richard MacCutchan 18-Mar-21 8:34am
Yes, but in English, "fair enough", means "not very good". :)
Maciej Los 18-Mar-21 8:46am
What statement should i use to say that person's knowledge is "quite/almost perfect"?
Richard MacCutchan 18-Mar-21 9:13am
If you say something like, "it seems good to me", then that suggests that you have a good opinion of the person's skills. English is a wonderful language, you can insult someone by offering them praise. :)) Does Polish have the same features?
Maciej Los 18-Mar-21 9:47am
Thank you for clarification, I'm sorry, i didn't mean to offend you. :)
Oh, yeah! Polish language offers several futures, including regional dialects.

Disclaimer: below content is not intended to people without sense of humor :)

The most interesting word in Poland is "kurwa" (pronunciation - similar to curve). As you suspect, this word is used when we want to say about some person in very insulting way. But this word can be used with adjective to "colorize" emotions. For eaxmple: "What a wanderful world!" -> "Kurwa, ale piękny świat"!

Richard MacCutchan 18-Mar-21 10:07am
No, you did not offend me - I know you never would. And I fully understood what you were trying to say; it is just that putting two "good" words together can be misinterpreted.

I wonder how you feel about me saying, "Twoje umiejętności programistyczne są wystarczające."?
Maciej Los 18-Mar-21 10:28am
You're right, Richard. This sounds similar to: "you are good programmer, but..." The problem is in that "but". Thank for pointing me that out.
The problem is very similar to this one: Need help on SQL query on value check[^].

Good luck!


SELECT [Level], [Check], Cnt = CASE
  WHEN [Level] = 1 AND LEAD([Level]) OVER(ORDER BY [Level]) =2 THEN
    (SELECT COUNT([Level]) FROM AAA WHERE [Level]=2 AND [Check] = 'N')

SQL Server 2019 | db<>fiddle[^]
Member 14936854 18-Mar-21 10:36am
Nope it’s different then this I have to populate count where level ends at 1
Maciej Los 18-Mar-21 10:52am
As i said, it's quite similar. The difference is in a subquery. That's all.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900