Solved

How to cfoutput a unioned query?

Posted on 2006-10-19
5
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 125 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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

630 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