Is this query even possible?

[Mike London]

Hi,

I have several columns in my query.

One is a select distinct and another is a count. The third Column is another count(*) BUT the WHERE clauses for all three queries are different.

I'm trying to display a unified listing of counts.

I tried to use a UNION, but I ended up with some rows having the data filled in and others that didn't (different parts of the UNION joining). There were no "dups" for the UNION to eliminate.

Any ideas on how to combine data like this with multiple where clauses?

I'd appreciate any and all ideas.

Thanks.

-- Mike

[Michael Matola]

LOL. Have you been following these
two threads?

Sometimes you can do queries like I think you're saying, sometimes not.

Whenever you're counting based on "different wheres" see if "case when .. then .. end" logic inside your count()s will do the trick.

Any and all ideas? Post some detailed table structures, sample data, and some queries you've tried.

Are you talking stuff like this?

select to_char( dttm_stamp , 'yyyy-mm-dd' ) as "Date registered" ,
       count( case when method = 'OverTheWeb' then 1 end ) "Over Web" ,
       count( case when method != 'OverTheWeb' then 1 end ) "Not Over Web" ,
       count( * ) "Total" ,
       round( count( case when method = 'OverTheWeb' then 1 end ) / count( * ) * 100 , 2 ) "Percent Over Web" ,
       round( count( case when method != 'OverTheWeb' then 1 end ) / count( * ) * 100 , 2 ) "Percent Not Over Web"
  from registration
 group by to_char( dttm_stamp , 'yyyy-mm-dd' )

(The "1" in "then 1" is a dummy value. Count() works on null vs. nonnull, so anything other than a null will do.)

(Sorry, I won't be around again until next week.)
[ June 23, 2006: Message edited by: Michael Matola ]

[steve souza]

Post a query of what you are trying to do. Sounds like you may be trying to do something like the following which i have done in sybase. You can also combine with the previous posters 'case' approach. I didn't try to run, but should work

  
select
  (select count(*) from table1) as table1count,
  (select count(*) from table2) as table2count,
  (select count(*) from table3) as table3count
 

  

[Mike London]

Because the queries for the counts operate on different tables, have different WHERE clauses and such, I'm thinking that a better approach would be to:

<USE JDBC CODE TO DO THE BELOW>

0. Get Connection

1. Create a temp table (CREATE TEMPORARY TABLE temp SELECT ...) with calculated fields for the values we can't create with a single query or a UNION.

2. Then iterate over the temp table issuing queries to UPDATE the calculated fields using queries that would have been (seemingly) impossible to do with a single query. Here, we would read whatever fields from the temp table we need to create the UPDATE statements to fill in the calculated fields in the temp table.

3. Display results.

=======

This approach would give you ultimate flexibility but would require a bit more work.

Comments?

Mike

[steve souza]

Post the sample implementation and it will be easier to make comments