Click here to Skip to main content
15,125,968 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Below is my requirement:
if category = 16 and count < 5000 then print
and if category is (24 or 35) and count is < 1000 then print

This is my script/data
<pre>
select n.category, count(*)
  from Staff n
 where n.category in (16,24,35)
 group by n.category


What I have tried:

This is my script/data
<pre><pre>
select n.category, count(*)
  from Staff n
 where n.category in (16,24,35)
 group by n.category
Posted
Updated 26-Feb-21 21:28pm

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]
   
You can use IF...ELSE (Transact-SQL) - SQL Server | Microsoft Docs[^] or CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]. The choice is yours.

CASE WHEN example:
SQL
SELECT category, cnt, CASE WHEN category = 16 AND cnt<5000 THEN 'category 16 less than 5000 cases'
    WHEN (category = 24 OR category =35) AND cnt<1000 THEN 'category 24, 35 less than 1000 cases'
    ELSE 'Other values' END AS myDescription
FROM
(
    SELECT n.category, count(*) cnt
    FROM Staff n
    WHERE n.category in (16,24,35)
    GROUP BY n.category
) A
   
Comments
varsh12 27-Feb-21 7:45am
   
Thanks Maciej Los
Maciej Los 27-Feb-21 8:24am
   
You're very welcome.
If my answer was helpful, please accept it (green button).
varsh12 27-Feb-21 10:17am
   
Yes. if I want to show only those row which is fulfill not all. Any help.
Like if category 16 and 35 are fulfill, then return only those 2 rows.
varsh12 27-Feb-21 11:45am
   
Now i want to return only those records which satisfied the condition.
category 16 count < 5000 and category 24 count < 1000 then print both but not 35
if category 24 and 35 count is < 1000 but category 16 count > 5000, then return only 24 and 35. like this.
i tried below but multiple oracle errors happening.

DECLARE count_1 number := 5000; Count_2 number:= 1000 ; beginIF (count_1< (SELECT count(*)    FROM staff n    WHERE n.category = 16    GROUP BY n.category) and   (Count_2 < (SELECT count(*)    FROM staff n<pre lang="text">
    WHERE n.category in (2,3) GROUP BY n.category)))
    then 
    select category,count_1 as count from staff where category in (1,2,3);
    
    else 
    (count_1 > (SELECT count(*)
    FROM staff n
    WHERE n.category = 1 GROUP BY n.category) and   (Count_2< (SELECT count(*)
    FROM staff n
    WHERE n.category in (2,3) GROUP BY n.category)))
    --then
    select category,count_1 as count from staff where category in (1,2,3);
 end if;
 end; 
Maciej Los 28-Feb-21 6:58am
   
This sounds like another question - use "Ask a question" widget.

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