#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Aggregates in union statements....need some help here...

Member Posts: 24

I've been wracking my brain for some time now. Here's the two tables I'm dealing with.

CIRC
SSN CHAR(9) NOT NULL
SCAN_CODE CHAR(6) NOT NULL
DATE_DUE DATE NOT NULL

CURRENT_DATE
TODAYS_DATE DATE NOT NULL *this is a fixed date of 06JAN00*

My issue is that I need to provide the SSN, total number of books checked out (which is any scan_code in the CIRC table), total number of books overdue (which is any scan_code in CIRC table that has a DATE_DUE that is before TODAYS_DATE), and figure up the total fine for each patron (which is the SSN). The formula for the fine's is the days overdue * .15.

Here is what I have thus far....
[code]
SELECT SSN, COUNT(SCAN_CODE) "BOOKS OUT"
FROM CIRC
GROUP BY SSN
*this gives me each patron with a count of all books checked out for
*that patron

SELECT SSN, NULL, COUNT(SCAN_CODE) "BOOKS OVERDUE",
(SUM(TODAYS_DATE - DATE_DUE) * .15) "TOTAL FINE"
FROM CIRC, CURRENT_DATE
WHERE TODAYS_DATE > DATE_DUE
GROUP BY SSN
*this gives me each patron with a count of overdue books and the total *fine for that patron
[/code]

However, I need to put these together somehow into one nice piece of output. I've tried doing a union, and was told the NULL's would hold the place for the other values, but I guess that won't work with aggregates. I am at a loss. Can anyone help me? Even just some info on how to better use aggregates in a union would help. Hell, encouraging words would help at this point :-D

[italic]
[b]Discipline[/b] - The desire to do the right thing in the absence of both leadership and consequence.
[/italic]

• Member Posts: 6

I don't actually think you need a union to accomplish this. If you can have all the result sets in a single column this will work. Provided is the querry , the results, and the data I used to test it. This query works on Oracle, but will require some modification for SQL Server and Oracle.

select SSN,
count(*) "BOOKS OUT",
SUM( CASE WHEN TODAYS_DATE > DATE_DUE THEN 1 ELSE 0 END ) "BOOKS OVERDUE",
SUM( CASE WHEN TODAYS_DATE > DATE_DUE THEN 1 ELSE 0 END ) * 0.15 "TOTAL FINE"
FROM
CIRC, CURRENT_DATE
group by
SSN;

SSN BOOKS OUT BOOKS OVERDUE TOTAL FINE
--------- ---------- ------------- ----------
AAA 4 2 .3
BBB 7 3 .45
ccc 4 0 0

create table CIRC (
SSN CHAR(9) NOT NULL,
SCAN_CODE CHAR(6) NOT NULL ,
DATE_DUE DATE NOT NULL
);

create table CURRENT_DATE
(
TODAYS_DATE DATE NOT NULL
);

insert into current_date values ( sysdate );

insert into circ values ( 'AAA', 'BBBB', sysdate - 1);
insert into circ values ( 'AAA', 'BBBB', sysdate - 2);
insert into circ values ( 'AAA', 'BBBB', sysdate + 2);
insert into circ values ( 'AAA', 'BBBB', sysdate + 1);

insert into circ values ( 'BBB', 'BBBB', sysdate - 1);
insert into circ values ( 'BBB', 'BBBB', sysdate - 1);
insert into circ values ( 'BBB', 'BBBB', sysdate - 2);
insert into circ values ( 'BBB', 'BBBB', sysdate + 2);
insert into circ values ( 'BBB', 'BBBB', sysdate + 1);
insert into circ values ( 'BBB', 'BBBB', sysdate + 2);
insert into circ values ( 'BBB', 'BBBB', sysdate + 1);

insert into circ values ( 'CCC', 'BBBB', sysdate + 1);
insert into circ values ( 'CCC', 'BBBB', sysdate + 2);
insert into circ values ( 'CCC', 'BBBB', sysdate + 3);
insert into circ values ( 'CCC', 'BBBB', sysdate + 1);
• Member Posts: 4
a little change, in the second row of query you must write

SUM( CASE WHEN SCAN_CODE IS NOT NULL then 1 else 0) "BOOKS OUT",

bye
i'm disciplined too. :-)

: I don't actually think you need a union to accomplish this. If you can have all the result sets in a single column this will work. Provided is the querry , the results, and the data I used to test it. This query works on Oracle, but will require some modification for SQL Server and Oracle.
:
:
: select SSN,
: count(*) "BOOKS OUT",
: SUM( CASE WHEN TODAYS_DATE > DATE_DUE THEN 1 ELSE 0 END ) "BOOKS OVERDUE",
: SUM( CASE WHEN TODAYS_DATE > DATE_DUE THEN 1 ELSE 0 END ) * 0.15 "TOTAL FINE"
: FROM
: CIRC, CURRENT_DATE
: group by
: SSN;
:
: SSN BOOKS OUT BOOKS OVERDUE TOTAL FINE
: --------- ---------- ------------- ----------
: AAA 4 2 .3
: BBB 7 3 .45
: ccc 4 0 0
:
: create table CIRC (
: SSN CHAR(9) NOT NULL,
: SCAN_CODE CHAR(6) NOT NULL ,
: DATE_DUE DATE NOT NULL
: );
:
: create table CURRENT_DATE
: (
: TODAYS_DATE DATE NOT NULL
: );
:
: insert into current_date values ( sysdate );
:
: insert into circ values ( 'AAA', 'BBBB', sysdate - 1);
: insert into circ values ( 'AAA', 'BBBB', sysdate - 2);
: insert into circ values ( 'AAA', 'BBBB', sysdate + 2);
: insert into circ values ( 'AAA', 'BBBB', sysdate + 1);
:
: insert into circ values ( 'BBB', 'BBBB', sysdate - 1);
: insert into circ values ( 'BBB', 'BBBB', sysdate - 1);
: insert into circ values ( 'BBB', 'BBBB', sysdate - 2);
: insert into circ values ( 'BBB', 'BBBB', sysdate + 2);
: insert into circ values ( 'BBB', 'BBBB', sysdate + 1);
: insert into circ values ( 'BBB', 'BBBB', sysdate + 2);
: insert into circ values ( 'BBB', 'BBBB', sysdate + 1);
:
:
: insert into circ values ( 'CCC', 'BBBB', sysdate + 1);
: insert into circ values ( 'CCC', 'BBBB', sysdate + 2);
: insert into circ values ( 'CCC', 'BBBB', sysdate + 3);
: insert into circ values ( 'CCC', 'BBBB', sysdate + 1);
:

• Member Posts: 6
The case isn't needed because the table definition specifies that the column can't ever be null. My assumption with the query was that this table only held a list of the books that people had checked out based on the fact that every row in the table has a check_out date.

Ryan

: a little change, in the second row of query you must write
:
: SUM( CASE WHEN SCAN_CODE IS NOT NULL then 1 else 0) "BOOKS OUT",
:
: bye
: i'm disciplined too. :-)
:
:

• Member Posts: 4
Touch. You are right.

Ricardo

: The case isn't needed because the table definition specifies that the column can't ever be null. My assumption with the query was that this table only held a list of the books that people had checked out based on the fact that every row in the table has a check_out date.
:
: Ryan
:
: : a little change, in the second row of query you must write
: :
: : SUM( CASE WHEN SCAN_CODE IS NOT NULL then 1 else 0) "BOOKS OUT",
: : * instead of count(*)
: :