Click here to Skip to main content
14,865,126 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
Posted
Updated 18-Mar-21 1:17am
v2
Comments
Maciej Los 18-Mar-21 7:14am
   
Show us what you have tried.

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

See SQL COUNT(), AVG() and SUM() Functions[^]
   
Comments
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
   
5ed!
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
   
Oooopss...
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"!

:D
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!

[EDIT]

SQL
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')
  ELSE NULL END
FROM AAA a


SQL Server 2019 | db<>fiddle[^]
   
v2
Comments
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