Overall Total in a Query

I was asked a question today by someone who emailed me. I like getting the occasional question as sometimes it either makes me learn new things or sharpens my focus on something I already know. I often don't have time to answer questions but I try as often as I can.

Today's question didn't teach me anything new but it was a fun 15 minute distraction. I like SQL and coming up with solutions.

Here's the question (names removed to protect the innocent):

Subject: Postgres query

Hello,

My name is xxxxxxx and I saw your link on the web and wonder if you have any example that can do the following view.

I have a table called agent_sales, in it I have an agent_link code and a Invoicesales records. I want to create a view that will sum(invoicesales) group by agent_link then create a percentage column of the summed(invoice sales to the overall grand total of the invoiced sales)

e.g.

agent link     invoice sales

   1               100

   2               300

   1               500

   2               400

When I sum by agent link I get 1= 600 and 2 = 700

Then I want to calculate a percentage or 1 and 2 to the total so it would work out to be 1 = 600/1300 & 2 = 700/1600

Can you assist me?

Regards

I have had to do these kinds of queries often so it didn't take long to put it together. The first time I had to do one if these, though, it took me quite a while to get it right.

Oracle provides some analytical queries that make these queries extremely easy to write and very performant. Since this was postgres, I took a different approach.

create table agent_sales (

  agent_link int,

  invoice_sale int );

insert into agent_sales

  (agent_link, invoice_sale)

values

  (1, 100), (2,300), (1, 500), (2, 400);

CREATE VIEW ag_sales AS

select agent_link, inv_sales, inv_sales / tot as overall_precentage

from

  (select agent_link, sum(invoice_sale)::float as inv_sales

     from agent_sales

     group by agent_link) as link_sales,

  (select sum(invoice_sale)::float as tot

     from agent_sales) as total_sales

Output:

SQL> select * from ag_sales;



 AGENT_LINK                INV_SALES       OVERALL_PRECENTAGE

----------- ------------------------ ------------------------

          2                    700.0       0.5384615384615384

          1                    600.0      0.46153846153846156



SQL>

Obviously, there are plenty of ways to answer most any question but this is the way I answered this one. How would you have done it?

postgres query