Menu
Can someone please help me to write a query to obtain the TCS and TRS?
ID Jan Feb Mar TRS
1 4 5 6 15
2 5 5 5 15
3 1 1 1 3
TCS 10 11 12
Both TCS (Total Column Sum) and TRS (Total Row Sum) are new column and row respectively which gives their.
453
The SUM() function returns the total sum of a numeric column.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.
Discussion: Use ORDER BY if you want to order rows according to a value returned by an aggregate function like SUM() . The ORDER BY operator is followed by the aggregate function (in our example, SUM() ). DESC is placed after this function to specify a descending sort order.
In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set.
You can use GROUP BY and WITH ROLLUP, like this:
SELECT
id
, SUM(jan) as jan
, SUM(feb) as feb
, SUM(mar) as mar
, SUM(jan+feb+mar) as TRS
FROM test
GROUP BY id WITH ROLLUP
Live demo on sqlfiddle.
88
This query will do the job
select cast(id as varchar(20)), Jan, Feb, Mar , Jan + Feb + Mar as TRS
from table1
union all
select 'TCS' as id, SUM(Jan) Jan, SUM(Feb) Feb, SUM(Mar) Mar, null as TRS
from table1
The first column will be returned as varchar as this way you have a mix of integers (id) and the text TCS.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
