Need SQL query for on count

Question

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 18-Mar-21 0:16am

Member 14936854

Updated 18-Mar-21 1:17am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 18-Mar-21 7:14am

Show us what you have tried.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Answer 1 · 2021-03-18T00:48:00

Solution 1

Probably something like:

SQL

SELECT COUNT(Level) FROM [table_name] WHERE Check='N';

See SQL COUNT(), AVG() and SUM() Functions[^]

Posted 18-Mar-21 0:48am

Richard MacCutchan

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.

Maciej Los · Answer 2 · 2021-03-18T01:17:00

Solution 2

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[^]

Posted 18-Mar-21 1:17am

Maciej Los

Updated 18-Mar-21 4:49am

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.