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?

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.