2 Replies - 447 Views - Last Post: 24 February 2018 - 04:16 AM Rate Topic: -----

#1 NecroWinter   User is offline

  • D.I.C Regular

Reputation: 38
  • View blog
  • Posts: 343
  • Joined: 21-October 11

Using a count and a case statement in one query

Posted 23 February 2018 - 09:04 PM

I'm pretty much out of ideas on how to get this to work.I haven't really used SQL in several years so there's a lot I don't remember.

So here is what I would like to happen:

1. I return the rows where the Code field from table has the value 1208 AND estnumber = 1187216
2. Run a count on the selection, if 0 run a subquery
3. If >0 run a different subquery


I didn't get to the subquery part yet because I can't get this to work correctly at all. Right now I just want it to return text.


Here is the latest attempt, I'm actually using db2 but maybe we can ignore that for now and i'll work that part out later because it says the syntax isnt correct, but other validators disagree (if you dont know anything about db2 just use standard sql when giving advice)


SELECT
  count(*) AS t
FROM
  table
WHERE
  (
    ESTNUMBER = 1187216
    AND CODE = 1208
  )
  AND CASE WHEN t = 0 THEN 'it is zero' ELSE 'it is not zero' END;



Is This A Good Question/Topic? 0
  • +

Replies To: Using a count and a case statement in one query

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2766
  • View blog
  • Posts: 10,958
  • Joined: 03-December 12

Re: Using a count and a case statement in one query

Posted 23 February 2018 - 09:25 PM

I think this is what you are after

CASE WHEN
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7397
  • View blog
  • Posts: 15,331
  • Joined: 16-October 07

Re: Using a count and a case statement in one query

Posted 24 February 2018 - 04:16 AM

You're thinking like an imperative programmer, not a declarative one. That is, SQL doesn't have sequential execution: it's all or nothing.

So, here's the start, the bit that works:
SELECT count(*) AS t
  FROM table
  WHERE ESTNUMBER = 1187216 AND CODE = 1208



Now, to check for the value of count(*), you by now know that WHERE isn't going to work. That's because COUNT is an aggregate function. To look at the result of such of function, you use HAVING.

For your CASE to work, you can move the it up into the area that can get count(*) results:
SELECT count(*) AS t
    (CASE WHEN count(*) = 0 THEN 'it is zero' ELSE 'it is not zero' END) as msg
  FROM table
  WHERE ESTNUMBER = 1187216 AND CODE = 1208



Note that "t" is an alias you've given the result of count(*). In most SQL implementations, that alias can't be leveraged in the rest of statement.

Now, for the either or kind of thing, it would be time to reconsider your approach and what you're really after. You'll probably ultimately have both result sets in your statement and choose how the results are served up.

Something like:
select a.id, a.ct, (case when a.ct=0 then b.amt else c.amt end) as amt
  from (select id, count(*) as ct from table1) a
    left join (select id, sum(amount) as amt from table2) b on a.id=b.id
    left join (select id, sum(amount) as amt from table3) c on a.id=c.id



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1