Combining values by field value 1

[prismtx]

I have some SQL code which I execute to feed to a spreadsheet for percentages. Out_code used to have a value of "0" or "1". Now it has multiple values for non-zero codes.

I want to make my output have only 2 lines per date: a line for "0" and a line for "1", but I have tried decode and case, but each only changes the value on the line, and does not combine the counts. What do I need to do to get a single count for all zero and non-zero values per date?

col "Departure Date" format a14
col  out_code    format a7  JUSTIFY right heading "Type"
col "Trips"        format 999,999

prompt Trips...
select (substr(to_char(depart_date), 5, 2) || '/' ||
       substr(to_char(depart_date), 7, 2) || '/' ||
       substr(to_char(depart_date), 1, 4)) as "Departure Date",
       CASE WHEN (out_code = 0) THEN '0'
            ELSE '1' 
       END  as out_code,
       count(*) as "Trips"
from departure_table
where depart_date >= to_char(sysdate-10, 'yyyymmdd')
group by depart_date, out_code
order by to_date(depart_date, 'yyyymmdd'), out_code;

Produces this output:
Departure Date Type Trips
-------------- ------- ----------
12/08/2008 0 790
12/08/2008 1 1
12/08/2008 1 1
12/08/2008 1 14
12/08/2008 1 53
12/08/2008 1 2482
12/12/2008 0 750
12/12/2008 1 1
12/12/2008 1 1
12/12/2008 1 6
12/12/2008 1 37
12/12/2008 1 2539

This is the output I want:
Departure Date Type Trips
-------------- ------- ----------
12/08/2008 0 790
12/08/2008 1 2551
12/12/2008 0 750
12/12/2008 1 2584

 

[ChrisHunt]

The problem is here:

group by depart_date, [red]out_code[/red]

You're grouping by the raw out_code, so it's giving you one row for each of the non-zero out codes.

You need to group by the same CASE or DECODE expression that you use in the SELECT clause.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[prismtx]

I tried doing the case as

CASE WHEN (out_code = 0) THEN '0'
            ELSE '1' 
       END  as out_code2,

and grouping on depart_date, out_code2, but it says
ORA-00904: "OUT_CODE2": invalid identifier

Do I need to code the expression again in the Group clause?

 

[SantaMufasa]

Prismtx,

I suggest that you are working way too hard. I don't know what your default DATE format is for your session/installation, but regardless, you don't need to do all of the DATE processing...Oracle can do it for you.

Here is code that produces your results:

col "Departure Date" format a14
col "Trips"        format 999,999
select to_char(depart_date,'mm/dd/yyyy') as "Departure Date",
      decode(out_code,0,0,1) "Type",
      count(*) "Trips"
from departure_table
where depart_date >= sysdate-10
group by depart_date, decode(out_code,0,0,1)
order by depart_date, decode(out_code,0,0,1)
;

Departure Date       Type    Trips
-------------- ---------- --------
12/08/2008              0      790
12/08/2008              1    2,551
12/12/2008              0      750
12/12/2008              1    2,584

Let us know your thoughts on the above code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[prismtx]

Thanks Mufasa,
The depart_date is not a date field, so I did a to_date on it and it worked great. Made the code much cleaner. Thanks for your help!

select to_date(depart_date,'yyyymmdd') as "Departure Date",
      decode(out_code,0,0,1) "out_code",
      count(*) "Trips"
from departure_table
where depart_date >= to_char(sysdate-10, 'yyyymmdd')
group by depart_date, decode(out_code,0,0,1)
order by depart_date, decode(out_code,0,0,1);

 

[SantaMufasa]

I'm glad it worked for you, Prismtx, and thanks for the

.

The depart_date is not a date field...

Prismtx, why is "depart_date" NOT a DATE field? There is no possible benefit to NOT having it be a DATE column...Notice that virtually every time you want to use "depart_date" (as a non-DATE expression), you must do extra processing to deal with it properly.


Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[prismtx]

I was surprised when I first started working on the system that it was not a date field, but its a vendor supplied system and we have no control over it or the coding. We can only write reports off of the data.