• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is this query even possible?

 
Mike London
Ranch Hand
Posts: 1220
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
whippersnapper
Ranch Hand
Posts: 1826
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?



(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
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

 
Mike London
Ranch Hand
Posts: 1220
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Post the sample implementation and it will be easier to make comments
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic