grouping issues 2

[ksbrace]

So, I have these 3 queries that return the exact data that I want:

select assignedto, ifnull(count(*),0),portfolio, 
sum(ifnull(round(paymentamount*contingencyamount/100,2),0)) from payments where paymentstatus='PENDING' 
  group by assignedto;
  
  
  select assignedto, ifnull(count(*),0) ,portfolio, 
sum(ifnull(round(paymentamount*contingencyamount/100,2),0)) from payments where paymentstatus='POSTED' 
  group by assignedto, portfolio;
  
  select assignedto,ifnull(count(*),0) , portfolio, 
sum(ifnull(round(originalbalance*contingencyamount/100,2),0))
from dbase group by assignedto, portfolio;

I then want to insert this data into 3 tables and all seems to be ok:

insert into indLiqPendTotal(collector, numPayments,portfolio_name, totalPending)
select assignedto, ifnull(count(*),0),portfolio, 
sum(ifnull(round(paymentamount*contingencyamount/100,2),0)) from payments where paymentstatus='PENDING' 
  group by assignedto, portfolio;


insert into indLiqPostTotal(collector, numPayments,portfolio_name, totalPosted)
select assignedto, ifnull(count(*),0) ,portfolio, 
sum(ifnull(round(paymentamount*contingencyamount/100,2),0)) from payments where paymentstatus='POSTED' 
  group by assignedto, portfolio;


insert into indLiqNumAccts(collector, numAccts,portfolio_name, totalValue)
select assignedto,ifnull(count(*),0) , portfolio, 
sum(ifnull(round(originalbalance*contingencyamount/100,2),0))
from dbase group by assignedto, portfolio;

Finally, I want to put them into one table:

insert into indLiqReports(collector, portfolio_name, numAccts, numPendPayments, totalPending,
numPostPayments, totalPosted, totalNumPayments, totalValue)
select indLiqPostTotal.collector, indLiqPostTotal.portfolio_name, indLiqNumAccts.numAccts, 
indLiqPendTotal.numPayments, indLiqPendTotal.totalPending, indLiqPostTotal.numPayments, 
indLiqPostTotal.totalPosted, indLiqPendTotal.numPayments + indLiqPostTotal.numPayments,
indLiqNumAccts.totalValue from indLiqPendTotal, indLiqPostTotal, indLiqNumAccts
where indLiqPendTotal.collector = indLiqPostTotal.collector
and indLiqPendTotal.collector = indLiqNumAccts.collector group by collector, portfolio_name;

Unfortunately, when I run this statement, it's wrong (every collector has same numAccts and same totalValue):

select collector, portfolio_name as portfolio, numAccts as "number of accounts", numPendPayments,
totalPending, numPostPayments, totalPosted, totalNumPayments, totalValue, 
round((((totalPosted+totalPending)/totalValue)*100),2) as "LiqRate" from indLiqReports group by portfolio,collector;

Any help would be greatly appreciated!!! Thanks in advance.

 

[r937]

start by properly following standard sql rules for GROUP BY -- any non-aggregate expression (which is not a constant) in the SELECT clause must have those columns in the GROUP BY clause



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

r937, can you give me an example?

 

[r937]

sure, here's one --

SELECT collector
     , portfolio_name AS portfolio
     , numAccts AS "number of accounts"
     , numPendPayments
     , totalPending
     , numPostPayments
     , totalPosted
     , totalNumPayments
     , totalValue
     , round((((totalPosted+totalPending)/totalValue)*100),2) as "LiqRate" 
  FROM indLiqReports 
GROUP 
    BY collector
     , portfolio_name
     [COLOR=#EF2929], numAccts 
     , numPendPayments
     , totalPending
     , numPostPayments
     , totalPosted
     , totalNumPayments
     , totalValue[/color]

the ones in red are the ones you missed

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

Ok,
so even with the extra grouping my query is wrong. The three initial 3 queries are returning the correct data. I don't understand why the final one is completely off.

http://www.mediafire.com/?qh7hbnyk98jhxc5

are my result files: pending, posted, numAccts are correct. The final_table is wrong. Any help would be great!

 

[r937]

what's wrong with it? you said it's "completely off" without any indication of how or why

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

well, that is why I included the link so you could actually see the data. It's listing the same collector several times for the same portfolio. It should list the collector once per portfolio with the associated data.

 

[r937]

with the associated data" is the clue here

you can remove columns from the GROUP BY as long as in the SELECT clause you put them inside aggregate functions

so...

SELECT collector
     , portfolio_name AS portfolio
     , [COLOR=#CC0000]SUM[/color](numAccts) AS "number of accounts"
     , [COLOR=#CC0000]SUM[/color](numPendPayments)
     , ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

Ok, so I'm working through this and this is my query:

select collector, portfolio_name as portfolio, sum(numAccts) as "number of accounts", sum(numPendPayments),
sum(totalPending), sum(numPostPayments), sum(totalPosted), sum(totalNumPayments), sum(totalValue), 
round((((totalPosted+totalPending)/totalValue)*100),2) as "LiqRate" from indLiqReports group by portfolio, collector, totalValue;

The only data that seems to be correct with the collector and portfolio are the following:
sum(numPostPayments), sum(totalPosted), sum(totalNumPayments).

The rest duplicate themselves with the associated collector.
ie: cdrayton always has 12 accts, 5 pending payments, 550.19 of pending payments, and 1935.50 of totalValue


cdrayton, 20111115NCABMG, 12, 5, 550.19, 4, 203.74, 9, 1935.50, 38.95
gfuqua, 20111115NCABMG, 1, 8, 457.46, 4, 98.00, 12, 85.31, 651.11
house, 20111115NCABMG, 25, 2, 480.00, 79, 5423.02, 81, 3720.50, 158.66
llanier, 20111115NCABMG, 1, 7, 870.00, 2, 80.54, 9, 320.56, 296.52
cdrayton, 20111205NCACCP, 12, 5, 550.19, 20, 1195.18, 25, 1935.50, 90.18
gfuqua, 20111205NCACCP, 1, 8, 457.46, 30, 1721.90, 38, 85.31, 2554.64
house, 20111205NCACCP, 25, 2, 480.00, 21, 1956.48, 23, 3720.50, 65.49
kcoplin, 20111205NCACCP, 3, 30, 3870.00, 6, 433.49, 36, 525.76, 818.53
llanier, 20111205NCACCP, 1, 7, 870.00, 4, 318.62, 11, 320.56, 370.79
rbarnes, 20111205NCACCP, 1, 3, 325.11, 5, 409.52, 8, 308.63, 238.03
tbrown, 20111205NCACCP, 66, 24, 4421.48, 6, 340.00, 30, 26713.00, 17.82
cdrayton, 20111205NCAVIP, 12, 5, 550.19, 6, 380.79, 11, 1935.50, 48.10

Obviously, I'm not doing something correct but I don't know what that something is. Any help is always greatly appreciated. Thanks in advance!

 

[IanWaterman]

Grouping by TotalValue is your problem

Change last 2 lines to

,
round((((sum(totalPosted)+sum(totalPending))/sum(totalValue))*100),2) as "LiqRate"
from indLiqReports
group by portfolio, collector;

Ian

 

[r937]

any chance i could get you to apply some formatting to your queries? it's awfully hard to read and understand them all strung out like that

SELECT collector
     , portfolio_name AS portfolio
     , sum(numAccts) AS "number of accounts"
     , sum(numPendPayments)
     , sum(totalPending)
     , sum(numPostPayments)
     , sum(totalPosted)
     , sum(totalNumPayments)
     , sum(totalValue)
     , ROUND(((([COLOR=#CC0000]totalPosted[/color]+[COLOR=#CC0000]totalPending[/color])/totalValue)*100),2) AS "LiqRate" 
  FROM indLiqReports 
GROUP 
    BY portfolio
     , collector
     , totalValue

notice that you still have two non-aggregate columns in the SELECT clause that aren't in the GROUP BY clause

also, since totalValue is in the GROUP BY clause, every different value of it will be listed separately, which is perhaps part of your confusion

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ksbrace]

absolutely! Sorry for the shabby copy/pasting. I was out yesterday due to illness. I think we are good and I really appreciate your help and patience. Boss seems happy now!