Solved

How to cfoutput a unioned query?

Posted on 2006-10-19
5
182 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I had to play with it some to get it to work within Oracle, but it did work.

Thanks again trailblazzry55.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
website does not load without www 12 65
Script 12 114
Reverse Proxy Server 6 69
Why addon domain redirects to the original domain? 8 58
This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
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…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now