SQL Question

[tamkas]

Hi, I was wondering about this table and the sql statement:

This is the sample Invoice table:

Source | Items
IG SEL
IG ANB
Null BEL
IG REN
Null REN

I like to count how many items are in NULL and IG. This is my SQL statement:

Select Count(a.Source) as numIG
Select Count(b.Source) as numNull
From Invoice a, Invoice b
where a.Source Like '%IG' and b.Source is null

I would like to get table like this one with my sql statement.

numIG | numNull
3 | 2

numIG is the total count of items in IG Source and numNull is the total count of items in null Source from Invoice table.

Thank you. Please let me know if you need any more information

 

[r937]

select sum(case when source = 'IG'  then 1 end) as numIG
     , sum(case when source is null then 1 end) as numNull
  from Invoice

r937.com | rudy.ca

 

[PHV]

SELECT SUM(CASE WHEN Source = 'IG' THEN 1 ELSE 0 END) numIG
, SUM(CASE WHEN Source IS NULL THEN 1 ELSE 0 END) numNull
FROM Invoice
WHERE Source Like '%IG' OR Source IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlexCuse]

SQL Question? Not many of those in here ;-)

But seriously, welcome to tek-tips. I think that you might find this beneficial (or any of the FAQ's in PHV's signature)

faq222-2244

You will find you get much more out of the site if you provide a wee bit more detail in your question.

Good Luck,

Alex





Ignorance of certain subjects is a great part of wisdom

 

[tamkas]

I got this error when I used the sql statement.

Expected identifier, literal, or parameter, found keyword "WHEN" (to use a keyword as an identifier, enclose it in double quotes)

Do you guys have any suggestion?

 

[r937]

yes, we have a suggestion: please show the exact query that produced this error



r937.com | rudy.ca

 

[tamkas]

hi r937,

the exact query:

select sum(case when source = 'IG' then 1 end) as numIG
, sum(case when source is null then 1 end) as numNull
from Invoice

 

[PHV]

So, you don't have an ANSI compliant RDBMS.
Which engine are you using ?

 

[r937]

it can really only be access

select sum(iif(source = 'IG',1,0)) as numIG
, sum(iif(isnull(source),1,0)) as numNull
from Invoice

r937.com | rudy.ca