CODEWITHSUNDEEP
sqlsum
Kong Hong
Kong Hong

Reputation: 45

sql : how to get the sum of sums

The below I can get the result of sub_shares, but I don't know how to get the result sum(sub_shares) by using the result of sub_shares for the below code:

How to do ?

SELECT * , 
Client.client_chi_name, 
Client.client_eng_name, 
SUM( shares_no ) AS sub_shares
FROM Shareholder
LEFT OUTER JOIN Client ON Shareholder.client_id = Client.client_id
WHERE Shareholder.com_no = 2040628
GROUP BY Shareholder.client_id
ORDER BY SUM( shares_no ) DESC,
Shareholder.date_of_register DESC

Table Shareholder

Thank you very much for your help & support.

Upvotes: 0

Views: 4999

Answers (4)

Jeff Y
Jeff Y

Reputation: 111

You could remove your GROUP BY and do the following:

SUM( shares_no ) OVER (PARTITION BY Client.client_id) AS sub_shares
SUM( shares_no ) AS total_shares,

You'd also need to start with SELECT DISTINCT or you'd get duplicates.

Upvotes: 0

Satish Kumar sonker
Satish Kumar sonker

Reputation: 1288

you can use CTE (Commom Table Expression) to accomplish the same task

with cte as
(
SELECT * , 
Client.client_chi_name, 
Client.client_eng_name, 
SUM( shares_no ) AS sub_shares
FROM Shareholder
LEFT OUTER JOIN Client ON Shareholder.client_id = Client.client_id
WHERE Shareholder.com_no = 2040628
GROUP BY Shareholder.client_id
ORDER BY shares_no  DESC,
Shareholder.date_of_register DESC
)

select cte.client_chi_name,cte.client_eng_name,SUM(cte.sub_shares) sub_shares from cte   --you can access more column which is present in * 
GROUP BY client_chi_name,client_eng_name --place extra column yor are accessing

Upvotes: 1

asb
asb

Reputation: 412

you can use below code as per your need,

either 

GROUP BY Shareholder.client_id WITH ROLLUP

or

GROUP BY Shareholder.client_id WITH CUBE

For detail understanding, please refer technet

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

SELECT * , 
  Client.client_chi_name, 
  Client.client_eng_name, 
  SUM( shares_no ) AS sub_shares,
  (select sum(shares_no) FROM Shareholder
      LEFT OUTER JOIN Client ON Shareholder.client_id = Client.client_id
      WHERE Shareholder.com_no = 2040628) as sum_of_sum
FROM Shareholder
LEFT OUTER JOIN Client ON Shareholder.client_id = Client.client_id
WHERE Shareholder.com_no = 2040628
GROUP BY Shareholder.client_id
ORDER BY SUM( shares_no ) DESC,
Shareholder.date_of_register DESC

You can add just one more column with subselect

Upvotes: 0

Related Questions