# SQL with multiple counts

Peter Primrose
Ranch Hand
Posts: 755
Hi guys,

Here�s my SQL for you.

Table

Rome S
Rome D1
Rome D2
Rome D1
Geneva S
Geneva S
Geneva D1
Geneva D1
Tel-Aviv T1
Tel-Aviv T1
Tel-Aviv T1
Tel-Aviv D1
Tel-Aviv D1

I�m trying to count how many S, D and T are there (mind that there might be D1 and D2)

I tried this:
SELECT city, type, count(type) as totalS, count(distinct type) as totalD
FROM myTable
WHERE ID=213 AND type='S' or type='Double(1)'
GROUP BY city

But the result are not as I expected

It should be:

City/ total s/ total d/ total t
Rome 1 2 0
Geneva 2 1 0
Tel Aviv 0 1 1

Michael Matola
whippersnapper
Ranch Hand
Posts: 1826
5
I think I understand your general question, but not some of the specifics.

I�m trying to count how many S, D and T are there (mind that there might be D1 and D2)

Does that mean that D1 and D2 should be counted as Ds or not?

But the result are not as I expected

It should be:

City / total s/ total d/ total t
Rome 1 2 0
Geneva 2 1 0
Tel Aviv 0 1 1

Huh? I understand the results you're expecting in the "total s" column, but "total d" and "total t" columns don't make sense to me, given the data you've provided.

Anyhow, take this query below as a starting point. It gets the "total s" column right. "total d" and "total t" we can tweak once you clarify how the D1/D2/T1/T2 stuff is supposed to work.

Note several things:

- I chose the "case when...end" construct instead of decode() or if() or the like because (1) it's fairly platform independent, (2) the syntax is cleaner, in my opinion, and (2) it allows us to use operators other than equals ("like", in this case).

- The '1' is a completely bogus constant value. I could just as easily used case when type = 'S' then 'Peter Primrose' end or something like that. The thing to remember when doing this kind of counting is that count( field ) counts 1 for any row where field isn't null. So the job of our case statement is to convert whatever we want to count into something that isn't null. I used '1' by convention. (Some folks like using '1'; others think it's confusing.)

Peter Primrose
Ranch Hand
Posts: 755
Michael Matola

THANK YOU SO MUCH. I learned somthing new today.

thanks it works!