posted 19 years ago
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.)
