SQL Query; how to display info only where the number of entries in one row is greater than the number of entries in another?

Question

Super simplified code:

SELECT lots_of_stuff.
 , A.more_stuff
 , B.stuff
 , C.things
FROM
 table A,
 table B,
 table C,
where (B.more_stuff = A.stuff)
 and things and stuff
 and lots more things
 and this query has so much crap believe me
 and finally
 and count(select c.things from table C where c.things like 'CRED') 
    > count(select c.things from table C where c.things like 'PUR')
;

So the problem is that that last bit does not work (and I'm certain I am doing it wrong entirely, this was just one guess on how to do it.) I was wondering if someone could give me some suggestions.

What I am trying to do is only return the desired fields for cases in which the number of rows containing 'CRED' in a particular field are greater than the number of rows containing 'PUR' in a particular field. (The same field, if that can simplify things.) I would like them to be returned regardless of if 'CRED' or 'PUR' are part of longer words (credit/purchase) or stand alone. They will always be all caps though.

Edit:

What I'm looking for is just those columns I specified

| More_Stuff | Stuff | Things |
|   dshsdh   |  dfh  |  tjra  |
|   ddh      |  ash  |  ytra  |
|   shsdh    |  fgh  |  sayh  |
|   hsdh     |  gnh  |  tshn  |

but only the rows for the customers that have more credit codes than purchase plans. So if they have 3 different entries in 'c.things' with something like "PHONE-CREDIT" or "OFFSET CRED." and 2 different entries in 'c.things' with something like "12 M PURCH PLAN" or "PROMO PURCHASE 36", I want their info to show up. So, when the number of rows with any credit codes is greater than the number of rows with any purchase plans. My current non-simplified query is already set to sort through all customers, I just need to specify which ones based on this filter.

Answer 1

I think you want something like this

 WITH cred_count AS
 (
    SELECT index_field, SUM(CASE WHEN field='CRED' THEN 1 ELSE 0 END) AS cred_count
    FROM some_table
    GROUP BY index_field
 ), pur_count AS
 (
    SELECT index_field, SUM(CASE WHEN field='PUR' THEN 1 ELSE 0 END) AS pur_count
    FROM some_table
    GROUP BY index_field
 )
 SELECT somestuff
 FROM some_table
 LEFT JOIN cred_count ON some_table.index_field = cred_count.index_field
 LEFT JOIN pur_count ON some_table.index_field = pur_count.index_field
 WHERE COALESCE(cred_count.cred_count,0) > COALESCE(pur_count.pur_count,0)

Note: You can change the WHEN part to be whatever you want to count (eg WHEN field like '%PUR%' would count rows containing the string PUR

Also, I'm making an assumption of having no entries count as 0 -- your business rule for this case might be different.

Answer 2

This can be achieved using WITH Clause in Oracle. The following code might be close to what you are looking for -

with ds1 as
(
    SELECT 
       lots_of_stuff
       , A.more_stuff
       , B.stuff
       , C.things,
       count(c.things) AS COUNT_CRED
    FROM
       table A,
       table B,
       table C,
    where 
       (B.more_stuff = A.stuff)
       and things and stuff
       and lots more things
       and this query has so much crap believe me
       and finally
       and c.things like 'CRED%'
   group by 
       lots_of_stuff.
       , A.more_stuff
       , B.stuff
       , C.things
   ),
 ds2 as
 (
    SELECT 
       lots_of_stuff.
       , A.more_stuff
       , B.stuff
       , C.things,
       count(c.things) AS COUNT_PUR
    FROM
       table A,
       table B,
       table C,
    where 
       (B.more_stuff = A.stuff)
       and things and stuff
       and lots more things
       and this query has so much crap believe me
       and finally
       and c.things like 'PUR%'
    group by 
       lots_of_stuff.
       , A.more_stuff
       , B.stuff
       , C.things
)
 SELECT DS1.*, ds2.*
 from ds1, ds2
 where count_cred > COUNT_PUR
 ;

Answer 3

To filter CRED and PUR in longer words use wildcards like % in the query.

like '%CRED%'  -- if CRED can be anywhere in the string
like 'CRED%'  -- if CRED is always at the beginning of the string

Note that if it is always at the beginning of the string, you can use an index on the column to make it run faster.

You cannot use an aggregate like count() in the where clause (unless Oracle supports it??)

You can group the rows and use HAVING but in your case, it is actually easier to move the count() inside the subqueries.

   SELECT lots_of_stuff.
 , A.more_stuff
 , B.stuff
 , C.things
FROM
 table A,
 table B,
 table C,
where (B.more_stuff = A.stuff)
 and things and stuff
 and lots more things
 and this query has so much crap believe me
 and finally
 and (select count(c.things) from table C where c.things like '%CRED%') 
    > (select count(c.things) from table C where c.things like '%PUR%')
;