Solved

How to cfoutput a unioned query?

Posted on 2006-10-19
5
191 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change local server setting in php 6 118
apply ddos protection on all network interface 2 139
System Analysis 5 71
change time in cron 4 107
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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