June 15, 2010 at 11:51 am
Does anyone have an idea on how to combine all these queries with different filter criterias?
select sum(bibal)as chking_total,count(acct)as chking_total_count
from cb_nd_daily
where dt='06/14/10'
select sum(bibal) as mma_total,count(acct) as mma_total_count
from cb_nd_daily
where dt='06/14/10'
and type in (52,74,77,78)
select sum(bibal) as sv_total,count(acct) as total_sv_count
from cb_sv_daily
where dt='06/14/10'
select sum(bibal) as cd_total,count(acct) as total_cd_count
from cb_cd_daily
where dt='06/14/10'
select sum(bibal)as br291total_chking,count(acct) as br291total_chking_count
from cb_nd_daily
where dt='06/14/10'
and br=291
select sum(bibal)as br291total_mma,count(acct) as br291total_mma_count
from cb_nd_daily
where dt='06/14/10'
and br=291
June 15, 2010 at 1:32 pm
Here's a solution that does all in one path.
However, you need to decide whether you really need the identical output several times just named different (especially the SUM(bibal) stuff)...
Side note: please note how I changed the conditional COUNT(acct) WHERE br=291 into a SUM (CASE WHEN THEN 1 ELSE 0 END) statement.
If that's not what you're looking for please post sample data in a ready to use format as described in the first link in my signature.
SELECT
-- query 1
SUM(bibal)AS chking_total,
COUNT(acct)AS chking_total_count,
-- query 2
SUM(bibal) AS mma_total,
SUM( CASE WHEN TYPE IN (52,74,77,78) AND acct IS NOT NULL THEN 1 ELSE 0 END) AS mma_total_count,
-- query 3
SUM(bibal) AS sv_total,
COUNT(acct) AS total_sv_count,
-- query 4
SUM(bibal) AS cd_total,
COUNT(acct) AS total_cd_count,
-- query5
SUM(bibal)AS br291total_chking,
SUM( CASE WHEN br=291 AND acct IS NOT NULL THEN 1 ELSE 0 END) AS br291total_chking_count,
-- query6
SUM(bibal)AS br291total_mma,
SUM( CASE WHEN br=291 AND acct IS NOT NULL THEN 1 ELSE 0 END) AS br291total_mma_count
FROM cb_nd_daily
WHERE dt='06/14/10'
June 15, 2010 at 8:44 pm
lmu92 (6/15/2010)
Here's a solution that does all in one path.However, you need to decide whether you really need the identical output several times just named different (especially the SUM(bibal) stuff)...
Side note: please note how I changed the conditional COUNT(acct) WHERE br=291 into a SUM (CASE WHEN THEN 1 ELSE 0 END) statement.
If that's not what you're looking for please post sample data in a ready to use format as described in the first link in my signature.
SELECT-- query 1
SUM(bibal)AS chking_total,
COUNT(acct)AS chking_total_count,
-- query 2
SUM(bibal) AS mma_total,
SUM( CASE WHEN TYPE IN (52,74,77,78) AND acct IS NOT NULL THEN 1 ELSE 0 END) AS mma_total_count,
-- query 3
SUM(bibal) AS sv_total,
COUNT(acct) AS total_sv_count,
-- query 4
SUM(bibal) AS cd_total,
COUNT(acct) AS total_cd_count,
-- query5
SUM(bibal)AS br291total_chking,
SUM( CASE WHEN br=291 AND acct IS NOT NULL THEN 1 ELSE 0 END) AS br291total_chking_count,
-- query6
SUM(bibal)AS br291total_mma,
SUM( CASE WHEN br=291 AND acct IS NOT NULL THEN 1 ELSE 0 END) AS br291total_mma_count
FROM cb_nd_daily
WHERE dt='06/14/10'
Lutz, got a question here ; from the OP's queries, if you could see, he/she is using 3 different tables to get the values ; so I guess, your code works only if all the 6 queries that OP is giving, access only one table.. So IMHO, i think only 4 out of the OP's original queries can be merged into a single one and the other 2 had to be exclusive.. PLease correct me if i am wrong..
June 16, 2010 at 10:27 am
ColdCoffee (6/15/2010)
...Lutz, got a question here ; from the OP's queries, if you could see, he/she is using 3 different tables to get the values ; so I guess, your code works only if all the 6 queries that OP is giving, access only one table.. So IMHO, i think only 4 out of the OP's original queries can be merged into a single one and the other 2 had to be exclusive.. PLease correct me if i am wrong..
Good catch!! Ttoally missed the different table names.. What a stupid mistake :blush:
If the column names need to be separate then we'll end up with three queries.
But if they should be merged, the fix is rather easy: split the query I provided into three queries (for cb_nd_daily, cb_sv_daily, and cb_cd_daily) and
merge the results using UNION ALL.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply