Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to cfoutput a unioned query?

Posted on 2006-10-19
5
Medium Priority
?
194 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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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 security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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