Howdy, Stranger!

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

Categories

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

vladimyr67vladimyr67 Member Posts: 24
Thanks in advance for any help you might offer.

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]

Comments

  • ryan_j_smithryan_j_smith 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);
  • abacotandilabacotandil 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",
    * instead of count(*)

    please reply you results
    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);
    :

  • ryan_j_smithryan_j_smith 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",
    : * instead of count(*)
    :
    : please reply you results
    : bye
    : i'm disciplined too. :-)
    :
    :

  • abacotandilabacotandil Member Posts: 4
    Touch. You are right.

    Thanks for reply.
    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(*)
    : :
    : : please reply you results
    : : bye
    : : i'm disciplined too. :-)
    : :
    : :
    :
    :

  • Chris BrownChris Brown USAMember Posts: 4,496 ✭✭

    _________ \ http://forcoder.org \ free video tutorials and ebooks about || MATLAB, Swift, PL/SQL, Assembly, Scratch, Visual Basic, Ruby, PHP, Python, R, C, C++, Visual Basic .NET, Java, Perl, Delphi, C#, Go, JavaScript, Objective-C COBOL, Apex, Dart, Crystal, Kotlin, LabVIEW, Prolog, Ada, Awk, ABAP, Transact-SQL, FoxPro, F#, Lisp, Clojure, Scheme, Erlang, Bash, Rust, ML, Alice, Julia, Lua, Logo, VBScript, D, Fortran, Scala, Hack, SAS || ____________

Sign In or Register to comment.