Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Can I Sum a Column?

    How can I sum a column within SQL (to be called via ColdFusion)? I know I can sum a row and get the results to display like this:

    name M F Total
    deptA 30 20 50
    deptB 10 10 20

    But I want to total to display underneath each column
    Last edited by turk99; 02-03-05 at 12:12.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps like this?
    Code:
    SQL> break on dummy
    SQL> column dummy noprint
    SQL> compute sum of sal on dummy
    SQL> compute sum of comm on dummy
    SQL> select empno, sal, comm, 'x' dummy
      2  from emp
      3  where empno in (7499, 7521);
    
         EMPNO        SAL       COMM
    ---------- ---------- ----------
          7521       1250        500
          7499       1600        300
               ---------- ----------
                     2850        800
    
    SQL>

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    i don't really understand what you just posted. break on dummy?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You wanted to sum columns in SQL and to display total underneath each column. That's what you said in your first post.

    So, here it is - columns "sal" and "comm" are summed and total is displayed below. Where's the problem?

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    That would work in sql*plus, but called in cold fusion I dont think so. You want something like this:

    Code:
    select dept, 
             sum(decode(gender, 'M', value_column, 0)) male,
             sum(decode(gender, 'F', value_column, 0)) female,
             sum(value_column) total
    from some_table
    group by dept
    If you want the headers, you could use a union all, and just union the 4 headers you want. Add a 5th column to the query that puts a 1 in the header query, and a 2 in the regular query. Alias this as sort order. Then just order by this column

    Code:
    select 'NAME', 'M', 'F', 'TOTAL', 1 sort_order
    from dual
    union all
    select dept, 
             sum(decode(gender, 'M', value_column, 0)) male,
             sum(decode(gender, 'F', value_column, 0)) female,
             sum(value_column) total,
             1 sort_order
    from some_table
    group by dept
    order by sort_order
    Hope that gives you a good start
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •