Forums Register Login

SQL with multiple counts

+Pie Number of slices to send: Send
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


thanks for any advise
+Pie Number of slices to send: Send
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.)
+Pie Number of slices to send: Send
Michael Matola

THANK YOU SO MUCH. I learned somthing new today.

thanks it works!
This cake looks terrible, but it tastes great! Now take a bite out of this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 1675 times.
Similar Threads
Help on java graph applet
multiple Select doesn't work.
Behaviour of Enums
Place Association
variable problem
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 13:21:14.