Sum of quoted premium using a subquery

143

October 20, 2018, at 6:20 PM

I want to calculate the sum of quoted premium for all the quote numbers present in the table with the latest transaction based on trans_id. The below query works for a single quote number, where the order by Transaction_id DESC limit 1 gives the highest transaction record.

SELECT  SUM(quoted_premium)  FROM policy 
     Where  quote_no= '1010'
     AND trans_type IN ('quote1','new', 'quote2')
     group by quote_no,Trans_id
     ORDER BY Trans_id DESC LIMIT 1;

Since the data is confidential, I'll give a sample table. There are multiple records for same quote no but different transaction id. I need to get the sum of a bunch of transaction id with recent records. The above query works fine for one quote number, but not for sum of all.

Trans_id    quote_no    trans_type  quoted premium  
1              1011         quote2      0   
1              1010         quote2      0   
2              1010         quote2     -16  
2              1010         quote2     -19  
2              1010         quote2     -51  
2              1010         quote2     776  
2              1010         quote2      0   
2              1010         quote2     381  
2              1010         quote2      0   
2              1010         quote2      0   
2              1010         quote2      59  
2              1010         quote2      0   
2              1010         quote2      0   
2              1010         quote2      9   
2              1010         quote2     194  
2              1010         quote2      0   
3              1010         quote2      0   
3              1010         quote2      0   
3              1010         quote2      1   
.               .             .         .
.               .             .         .
.               .             .         .

I need to match the above records to this below csv file. The total number of records present in the csv file are 39731

QUOTE_NO  New   Quote1   Quote2     GrandSum
1009     1425     -         -        1,425   
1010       -      0        556         556
1111       -      0       1,332      1,332       
.          .      .         .           .
.          .      .         .           .
                             sum     54,138,664
                             Avg     1363

So, when I use the above query to calculate the sum of quoted premium for quote no: 1010 it gives me the value 556. Similarly when I try to remove the condition for quote number and try to run the query it gives me a value of some quote number which had the latest transaction record.

I want to use subquery and do a self join to get the proper result, but I am unable to figure out.

Answer 1

You need a result set considering only the highest Trans_id for each quote_no. That's what you mean by most recent. Start with a subquery to get those Trans_id values. View on DB Fiddle

SELECT quote_no, MAX(Trans_id) Trans_id
  FROM policy
 GROUP BY quote_no;
| quote_no | Trans_id |
| -------- | -------- |
| 1010     | 3        |
| 1011     | 1        |

Next, you should write a query to fetch the GrandSum values, to convince yourself you have done your filtering correctly.

SELECT p.quote_no, SUM(p.quoted_premium) GrandSum
  FROM policy p
  JOIN (
             SELECT quote_no, MAX(Trans_id) Trans_id
              FROM policy
             GROUP BY quote_no
       ) sel ON p.quote_no = sel.quote_no AND p.Trans_id = sel.Trans_id
  WHERE trans_type IN ('quote1','new', 'quote2')
  GROUP BY p.quote_no

(The sample data in your question doesn't give a very interesting result set here, because your highest Trans_id values apply to boring records.)

Finally, adapt that query to do your presentation. Your presentation involves pivoting the rows for each trans_type so they appear as columns. That gets done with the SUM(IF... pattern.

SELECT p.quote_no, 
       SUM(IF(p.trans_type = 'new', p.quoted_premium, 0)) new,
       SUM(IF(p.trans_type = 'quote1', p.quoted_premium, 0)) quote1,
       SUM(IF(p.trans_type = 'quote2', p.quoted_premium, 0)) quote2,
       SUM(p.quoted_premium) GrandSum
  FROM policy p
  JOIN (
             SELECT quote_no, MAX(Trans_id) Trans_id
              FROM policy
             GROUP BY quote_no
       ) sel ON p.quote_no = sel.quote_no AND p.Trans_id = sel.Trans_id
  WHERE trans_type IN ('quote1','new', 'quote2')
  GROUP BY p.quote_no

Pro tip: SQL is declarative, not procedural. It's a language for declaring what sets of data you need from your tables. So, when designing this stuff think about sets: the set of latest transaction ids, the set of policy rows with the correct transaction types, and so forth.

POPULAR ONLINE

How to get a value from select with Ajax and use in PHP

How to check input value in a SQL query using CASE-WHEN

How to get element by xpath and inner text?

Where is coming Margin Top?

SQL - Selecting based on multiple entries in same table?

If my tables are setup like this:

108

MySQL

Find those companies whose employees earn a higher salary, on average, than the average salary at “Wipro”

I have tried one sql but not working

131

MySQL

Generating multiple indexes in a single postgres table

I have some source syntax that I need to convert from MySQL to postgreSQLIt seems that there is no analog in postgres for generating multiple indexes on a single table

107

MySQL

Is there a way to automate DELETE and INSERT in MySQL when new data is added?

I'm new to the whole database management game, so if I'm totally off base on this, and there's a better way to do this, please let me know

119