Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unanswered: sql query data horizontally with sum and condition

    I am trying to get data from the following query in a row with total but not succeed and getting error message. Please help and advise.

    Select empid,name,country,
    sum(case category when 'JOB' then sum(core+RR) else 0 end) as 'JOB-Sum',
    sum(case category when 'PRIVATE' then sum(core+PP) else 0 end) as 'PRIVATE-Sum',
    group by empid,name,country
    -------------------------------------------------------------------------------------------------------------

    Note: Following query is good for getting result in one row but i have to put sum calculation with 'THEN' and want result in a row.

    SELECT ID, MAX(case when Col1 = 'Yes' then 1 else 0 end) AS Col1,

    MAX(case when Col2 = 'Yes' then 1 else 0 end) AS Col2

    FROM MyTable

    GROUP BY ID

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ... and getting error message
    means nothing. Oracle returns an error code and error message. As we aren't mind readers nor sit behind your back so that we could see what's on your screen, it is YOU who should tell us what happened. The best option is to copy and paste the whole SQL*Plus session so that we could see it ourselves.

    As of your problem, well, it seems that you should remove the SUM function from the CASE statement and remove single quotes from the column alias; something like this:
    Code:
    sum(case 
          when category = 'JOB' then core + RR
          else 0 
        end
       ) as JOB_Sum,
    If you insist on JOB-Sum, then enclose it into DOUBLE quotes, not single ones.

    Also, next time properly format your code and use CODE tags improve readability.

  3. #3
    Join Date
    Apr 2008
    Posts
    4
    Thanks so much.

Posting Permissions

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