Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to cfoutput a unioned query?

Posted on 2006-10-19
5
Medium Priority
?
195 Views
Last Modified: 2013-12-24
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>&nbsp;</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.
 


0
Comment
Question by:kgarnto
  • 3
  • 2
5 Comments
 

Author Comment

by:kgarnto
ID: 17769592
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.
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17770431
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>
0
 
LVL 20

Expert Comment

by:trailblazzyr55
ID: 17770475
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>
0
 
LVL 20

Accepted Solution

by:
trailblazzyr55 earned 500 total points
ID: 17770524
sorry for all the posts, but this may work better, I'll be able to test it in the morning and put together something better if this doesn't work...

<cfquery name="qGetTotals" datasource="#ds#">
SELECT A.Country,
       A.State,
         A.UpCount + B.UpCount AS Up_Count,
         A.DownCount + B.DownCount AS Down_Count,
         A.InCount + B.InCount AS In_Count,
         A.OutCount + B.OutCount AS Out_Count
FROM (
      SELECT country,state,SUM(decode(event_status, 'UP', 1, 0)) UpCount,SUM(decode(event_status, 'DOWN', 1, 0)) DownCount,SUM(0) InCount,SUM(0) OutCount
      FROM  event_status
      WHERE stop_date IS NULL
      AND   active_flag = 'Y') A
      GROUP BY country, state
INNER JOIN (
      SELECT country,state,SUM(0) UpCount,SUM(0) DownCount,SUM(DECODE(event_status, 'IN', 1, 0)) InCount,SUM(DECODE(event_status, 'OUT', 1, 0)) OutCount
      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'
      GROUP BY country, state
) B
ON A.Country = B.Country AND A.State = B.State
<cfquery>
0
 

Author Comment

by:kgarnto
ID: 17798484
I had to play with it some to get it to work within Oracle, but it did work.

Thanks again trailblazzry55.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Integration Management Part 2
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question