aspose file tools*
The moose likes JDBC and the fly likes SQL with multiple counts Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL with multiple counts" Watch "SQL with multiple counts" New topic
Author

SQL with multiple counts

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
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


thanks for any advise
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1746
    
    2
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

Joined: Sep 10, 2004
Posts: 755
Michael Matola

THANK YOU SO MUCH. I learned somthing new today.

thanks it works!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL with multiple counts