asked on

How to cfoutput a unioned query?

I'm trying to combine two different queries into one. My union seems to work fine, but it also returns two rows for each record, when I really only want it to return one. I'm trying to get a count of different statuses, but each query requires different criteria. Is there a way to combine the output so that it only displays one line for each state.

<cfquery name="qGetTotals" datasource="#ds#">
select country, state, sum(decode(es.event_status, 'UP', 1, 0)) up_count, sum(decode(es.event_status, 'DOWN', 1, 0)) down_count,
sum(0) in_count, sum(0) out_count
from event_status es
where es.stop_date is null
and es.active_flag = 'Y'
UNION
select country, state, sum(0) up_count, sum(0) down_count,
sum(decode(es.event_status, 'IN', 1, 0)) in_count, sum(decode(es.event_status, 'OUT', 1, 0)) out_count,
from event_status es
where es.stop_date is not null
and es.active_flag = 'Y'
and trunc(es.start_date) >= '18-OCT-2006'
and trunc(es.start_date <= '19-OCT-2006'
<cfquery>

------------------------------
My output code:

<cfoutput query="qGetTotals" group="country">
<tr>
<td colspan="5" align="left" bgcolor="DFDFFF">#country#</td>
</tr>
<cfoutput group="state">
<tr>
<td> </td>
<td align="center">#state#</td>
<td align="center" valign="top">#UP_Count#</td>
<td align="center" valign="top">#DOWN_Count#</td>
<td align="center" valign="top">#IN_Count#</td>
<td align="center" valign="top">#OUT_Count#</td>
</tr>
</cfoutput>

My problem is that it returns this:
Country State Up Down In Out
------------------------------------------------------------------------------------
United States
Florida 3 4 0 0
Florida 0 0 4 6

How can I better write this code so that it will only return one row for each state with the desired results?

Thanks for any help you can provide.

kgarnto

ASKER

Excuse me, let me add to this issue:

If I take out the group by state, that's when I get the two rows for each state.

If I leave it in, I do only get one row, but it doesn't combine the counts, and it will leave 0 in a column that should have a count.

trailblazzyr55

Give this query a try... I'm not sure if it'll do the trick yet since I can't really test it here, but see if it works out the way you need...

You can use the group on the Country for the query output as well with this query...

<cfquery name="qGetTotals" datasource="#ds#">
SELECT A.Country, A.State, A.Up_Count, A.Down_Count, B.In_Count, B.Out_Count
FROM (
      SELECT country,state,SUM(decode(event_status, 'UP', 1, 0)) up_count,SUM(decode(event_status, 'DOWN', 1, 0)) down_count,SUM(0) in_count,SUM(0) out_count
      FROM event_status
      WHERE stop_date IS NULL
      AND active_flag = 'Y') AS A
INNER JOIN (
      SELECT country,state,SUM(0) up_count,SUM(0) down_count,SUM(DECODE(event_status, 'IN', 1, 0)) in_count,SUM(DECODE(event_status, 'OUT', 1, 0)) out_count
      FROM event_status es
      WHERE stop_date IS NOT NULL
      AND active_flag = 'Y'
      AND TRUNC(start_date) >= '18-OCT-2006'
      AND TRUNC(start_date <= '19-OCT-2006'
) AS B
ON A.Country = B.Country AND A.State = B.State
<cfquery>

trailblazzyr55

if the one I posted doesn't work, try this... again not tested, if you get any error, post them and we can go from there...

<cfquery name="qGetTotals" datasource="#ds#">
SELECT A.Country, A.State, A.Up_Count, A.Down_Count, B.In_Count, B.Out_Count
FROM (
      SELECT country,state,SUM(decode(event_status, 'UP', 1, 0)) up_count,SUM(decode(event_status, 'DOWN', 1, 0)) down_count,SUM(0) in_count,SUM(0) out_count
      FROM event_status
      WHERE stop_date IS NULL
      AND active_flag = 'Y') A
INNER JOIN (
      SELECT country,state,SUM(0) up_count,SUM(0) down_count,SUM(DECODE(event_status, 'IN', 1, 0)) in_count,SUM(DECODE(event_status, 'OUT', 1, 0)) out_count
      FROM event_status
      WHERE stop_date IS NOT NULL
      AND active_flag = 'Y'
      AND TRUNC(start_date) >= '18-OCT-2006'
      AND TRUNC(start_date <= '19-OCT-2006'
) B
ON A.Country = B.Country AND A.State = B.State
<cfquery>

ASKER CERTIFIED SOLUTION

trailblazzyr55

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kgarnto

ASKER

I had to play with it some to get it to work within Oracle, but it did work.

Thanks again trailblazzry55.