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
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)
agent link invoice sales
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?
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 (
invoice_sale int );
insert into agent_sales
(1, 100), (2,300), (1, 500), (2, 400);
CREATE VIEW ag_sales AS
select agent_link, inv_sales, inv_sales / tot as overall_precentage
(select agent_link, sum(invoice_sale)::float as inv_sales
group by agent_link) as link_sales,
(select sum(invoice_sale)::float as tot
from agent_sales) as total_sales
SQL> select * from ag_sales;
AGENT_LINK INV_SALES OVERALL_PRECENTAGE
----------- ------------------------ ------------------------
2 700.0 0.5384615384615384
1 600.0 0.46153846153846156
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?