How to avoid Group By working on every output?-CodePudding

I have a table like this:

LocationID  CountryName  CustomerAmount
C01        Australia     500
C02        Australia     200  
C03        China         100
C04        China         200
C05        Japan         50
C06        Canada        120

I want to find the "number of customers in each country" AND the total number of customers.

Now I have the following query:

select countryName, sum(CustomerAmount)
from test
group by countryName;

I obviously got this output:

 CountryName.  customerAmount
 Australia     700
 China         300 
 Japan         50
 Canada        120

But I want the output like this

 CountryName.  customerAmount    totalAmount
 Australia     700               1170
 China         300               1170
 Japan         50                1170
 Canada        120               1170

My problem is how can I put two same sum(customerAmount) side by side, but one is grouped by countryName, while the other just sum up all values in customerAmount table.

Thank you in advance!!!! I have to say sorry as my expression may be ambiguous.

CodePudding user response：

One easy way is just to use a sub-query like

select countryName, sum(CustomerAmount) customerAmount,
  (select Sum(customerAmount) from test) totalAmount
from test
group by countryName;

If you can use window functions (MySql 8 ) you can do

select countryName, sum(CustomerAmount) customerAmount,  
  sum(Sum(CustomerAmount)) over() totalAmount
from test
group by countryName;

note the nested sum().

CodePudding user response：

SELECT countryName, SUM(CustomerAmount), SUM(CustomerAmount) OVER()
FROM test
GROUP BY countryName;

I did not test this, but using the over clause should do what you are looking for as seen here.