get sum of columns in next column sql

Question

<table border="1">
  <tr><th>Pcode</th><th>TCode</th><th>TName</th><th>CCode</th><th>Rate</th><th>Total=sum(rates of all PCodes)</th></tr>
  <b><tr><td>12345</td><td>200</td><td>200</td><td>12</td><td>2000</td><td>8000</td></tr>
  <tr><td>12345</td><td>201</td><td>200</td><td>12</td><td>1000</td><td>8000</td></tr>
  <tr><td>12345</td><td>202</td><td>200</td><td>12</td><td>2000</td><td>8000</td></tr>
  <tr><td>12345</td><td>205</td><td>200</td><td>12</td><td>3000</td><td>8000</td></tr>
  <tr><td>12346</td><td>200</td><td>200</td><td>12</td><td>4000</td><td>32000</td></tr>
  <tr><td>12346</td><td>204</td><td>200</td><td>12</td><td>5000</td><td>32000</td></tr>
  <tr><td>12346</td><td>208</td><td>200</td><td>12</td><td>5000</td><td>32000</td></tr>
  <tr><td>12346</td><td>1235</td><td>200</td><td>12</td><td>3000</td><td>32000</td></tr>
  <tr><td>12346</td><td>12</td><td>200</td><td>12</td><td>7000</td><td>32000</td></tr>
  <tr><td>12346</td><td>100</td><td>200</td><td>12</td><td>8000</td><td>32000</td></tr>
  
</table>
<p>I have a table with Pcode, Tcode, TName, CCode, Rate. Need to find the sum under Pcode as Total in next column</p>

I want this table column '(Total)' to be fetched along with all columns in the below table(SQL).

I have tried ROLLUP and group by no solution yet.

the total to be group by Pcode and get that sum to the next column.

TIA,

Where is the table? Please also provide sample data and the desired output corresponding to that sample data. — trincot, May 26 '16 at 13:41
You should also include the query that you tried. Take a look at this: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Morpheus, May 26 '16 at 14:49

msheikh25 · Answer 1 · 2016-05-27T11:58:05.053

0

With your_table_CTE as (
    select C.x,A.y,B.x from DFG inner join A, inner join B, inner join C
)

select a.pcode, a.TCode, a.TName, a.CCode, a.Rate, b.Total
from your_table_CTE a
join (
  select pcode, sum(rate) as Total
  from your_table_CTE
  group by pcode
) b
on a.pcode = b.pcode

edited May 27 '16 at 11:58

answered May 26 '16 at 15:46

msheikh25

576
3
9

1

This is the right idea, but you need to also use `your_table` in the subquery and alias `sum(rate)` as `Total` in order for it to work. – Morpheus May 26 '16 at 16:13
your_table is not just a table its a select * from four different tables with inner joins. `your_table = select C.x,A.y,B.x from DFG inner join A, inner join B, inner join C` so `your_table` is a query not a table. – Cruzer May 27 '16 at 05:29
You can use a Common Table Expression with your query first then.. `with your_table_CTE as (select C.x,A.y,B.x from DFG inner join A, inner join B, inner join C) select ...` – msheikh25 May 27 '16 at 11:55

score 0 · Answer 2 · answered May 26 '16 at 16:10

You only need to use GROUP BY. This solution uses a Common Table Expression (replace tempTable with the name of your table):

;WITH TotalByPCode (PCode, Total) AS (
    SELECT PCode, SUM(Rate) FROM tempTable GROUP BY PCode
    )

SELECT R.PCode, R.TCode, R.TName, R.CCode, R.Rate, T.Total
FROM tempTable R JOIN TotalByPCode T ON R.PCode = T.PCode
;

get sum of columns in next column sql

2 Answers2