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

Postgres DDL Transaction Control Sucks

I come from an Oracle background so I am used to a few rules when it comes to DDL.

• Don't perform DDL in a production database until you've done it in a test instance.

• DDL is permanent unless you have flashback or want to restore.

That might sound like a limitation but I don't see it that way. You *SHOULD* test everything before running it in production. And flashback is much more powerful than simply having commit and rollback for DDL. Today I was trying out some changes to my code (that runs outside of the database) and needed to do a quick test that would delete some rows. I did a quick create table xyz as select * from abc; I ran a quick test on some prep data: select func(); The func() hit an error and rolled back. I fixed the error and reran the function. It worked so I ran my external program. It deleted the data and committed the changes. I then tried to put the records back in the first table from the second table. Oops. Second table no longer exists. The rollback from the stupid func() also rolled back my create table.

In Oracle, I would have several options to recover my data. For one, the create would automatically have committed. But in Oracle, I wouldn't have needed the table, I would just flashback and restore my data.

Fortunately, this was just a test bed database and the data isn't really critical. Had it been critical, I would have taken steps to be sure it was backed up. So, no big deal really.

And, yeah, I know Ingres and SQL Server (and probably Sybase) work like Postgres. Doesn't make it a good thing.

My next whine will be transaction control is stored procedures. But at least that will be coming from EnterpriseDB at some point.

postgres ddl