INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

grouping issues

grouping issues

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

CODE

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:

CODE

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:

CODE

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):

CODE

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.

RE: grouping issues

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

RE: grouping issues

(OP)
r937, can you give me an example?

RE: grouping issues

sure, here's one --

CODE

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
     , numAccts 
     , numPendPayments
     , totalPending
     , numPostPayments
     , totalPosted
     , totalNumPayments
     , totalValue 
the ones in red are the ones you missed

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

RE: grouping issues

(OP)
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!

RE: grouping issues

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

RE: grouping issues

(OP)
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.

RE: grouping issues

"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...

CODE

SELECT collector
     , portfolio_name AS portfolio
     , SUM(numAccts) AS "number of accounts"
     , SUM(numPendPayments)
     , ... 


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

RE: grouping issues

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

CODE --> mysql

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!

RE: grouping issues

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

RE: grouping issues

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

CODE

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 
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

RE: grouping issues

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close