CODEWITHSUNDEEP
sqlsql-server
user2169875
user2169875

Reputation: 13

Obtaining Counts

I am new to SQL and have been stuck on this piece of code for a couple days.

select distinct Count( AGVGLST.AGVGLST_PIDM ) "Count_AGVGLST_PIDM",
       Count( (select distinct pidm
       from donor
       where donor.pidm = agvglst_pidm
       and donor.cfae_cat IN ('ALMB','ALMX')) ) "Cfae"
  from AGVGLST
 where AGVGLST.AGVGLST_DESG ='1125'
       and AGVGLST.AGVGLST_FISC_CODE ='2010'
       and not exists(select 'x'
       from agvglst b
       where b.agvglst_pidm = agvglst_pidm
       and b.agvglst_desg <> '1125'
       and b.agvglst_fisc_code = '2010')

I am trying to get counts for only this desgination '1125' so I need to reference it twice and for some reason I am getting zero counts when I know there are donors who donated to only that specific desgination. I am sure it is something stupid I am missing.

Upvotes: 0

Views: 65

Answers (3)

Sebas
Sebas

Reputation: 21522

what if you run this query?

SELECT 
    COUNT(a.AGVGLST_PIDM) "Count_AGVGLST_PIDM",
FROM AGVGLST a
WHERE 
    a.AGVGLST_DESG      = '1125'
AND a.AGVGLST_FISC_CODE = '2010'
AND NOT EXISTS(
            SELECT 'x'
            FROM 
                agvglst b
            WHERE b.agvglst_pidm = a.agvglst_pidm
            AND b.agvglst_desg <> '1125'
            AND b.agvglst_fisc_code = '2010'
        )

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

When something that should be simple starts getting too complicaed; it's time to backup and start over:

select 
  AGVGLST_DESG,
  coun(*) as "Count_AGVGLST_PIDM"
from AGVGLST
where AGVGLST_DESG = '1125'
group by 
  AGVGLST_DESG

Any additional filter-fields get added to the select list and the group by list, and then tothe where clause.

Upvotes: 0

Ken Clark
Ken Clark

Reputation: 2530

   not exists(select 'x'
   from agvglst b
   where b.agvglst_pidm = agvglst_pidm
   and b.agvglst_desg <> '1125'
   and b.agvglst_fisc_code = '2010')

May be this is creating problems here. There can be donor whose designation is not '1125' but agvglst_fisc_code is '2010'. So, due to existance of records your Not exists is restricting '1125' to be counted.

Upvotes: 1

Related Questions