Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Adding sum of certain rows and cols to my query

hello everyone. Im really clueless in sql to say the least. I have the following query and it's result :


ON TOP of that I want to be able to add a column name TOTAL which will sum each row (for example first row Total=3).

Also I want to add TWO !ROWS! as sum of the targeted column's rows, so for example

I want to create a new Row with (SUM of status 2 4 and 5) and a total sum of all statuses like shown below :


Ive tried adding to the First query row (select * from) --> select status,1,2,3,5, 1+2+3+5 as Total aswell as COALESCE and even Rollup() but the results were nothing near.

something else I dont understand is, why whenever I change my select * statement to specific columns, it will fill the rows with the column's value's data?? for exaple if I changed my select to (select status, 1) from, it will fill the column 1 row's value to 1 ??:


Any help will be appreciated much, thanks !!

Answers

  • Rostislav Kushnirenko
    Rostislav Kushnirenko Member Posts: 17 Blue Ribbon

    Matters to attend any SQL course of your choice, search for string "SQL online course" - will give you tons of possibilities. For example: https://www.codecademy.com/learn/learn-sql. After going through at least few lessons, you will be able to answer your above questions on your own!

  • User_BY2GE
    User_BY2GE Member Posts: 3 Green Ribbon

    I know and i've looked into several guides yet I dont know how to adjust my query to provide the result I want. thanks anyways.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,937 Red Diamond
    edited Nov 15, 2022 1:30PM

    You can't do it with PIVOT. Use GROUP BY with ROLLUP or GROUPING SETS instead. For example:

    select  case grouping(deptno) when 1 then 'total' else to_char(deptno) end deptno,
            sum(case job when 'MANAGER' then sal end) dept_managers,
            sum(case job when 'SALESMAN' then sal end) dept_salesmen,
            sum(case job when 'CLERK' then sal end) dept_clerks,
            sum(case job when 'ANALYST' then sal end) dept_analysts
      from  emp
      group by rollup(deptno)
    /
    
    DEPTNO DEPT_MANAGERS DEPT_SALESMEN DEPT_CLERKS DEPT_ANALYSTS
    ------ ------------- ------------- ----------- -------------
    10              2450                      1300
    20              2975                      1900          6000
    30              2850          5600         950
    total           8275          5600        4150          6000
    
    SQL>
    

    SY.