New Job newbie SQL - help

empirix

Lads,

Getting my balls burned a bit in my new job here, basically looking at the sql code and whilst reading it is fine, my own little piece of it is having troubles, can anybody help me out here:

Select Count(*), Avg(NET_MARGIN), (INT_RTE), Sum(BAL_AMT), Sum(NET_BAL_FOR_PRNCPL)

From filename
Where Bal_for_int_ban = 'B'
And period_dte='2006-03-31'

Getting an error stating Net_Margin can't be found, its definitely in the table

Smoggy

What Database are you using ?

Not that, that matters, as you are doing a select that requires a group without any grouping.

SELECT COUNT(*), AVG(NET_MARGIN), INT_RTE, SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL)
FROM FileName
WHERE (Bal_for_int_ban = 'B') AND (period_dte = '2006-03-31')
GROUP BY INT_RTE

Should sort it, but doesnt explain why 'Net_Margin' is giving u there error ?
Also whats the count doing for you ?

empirix

Count is just counting all the records related(=B) i hope anyway, thanks for that code, tis great, ran it but still missing the net_margin. But anyway i am on the right track now

cheers

Smoggy

What Database are you running ? i might beable to be of more help if it sql server

Also try a simple select * from filename to make sure you are connected to the right instance of the table. If the results set return contains no 'net_margin' its time to look into why.

empirix

Smog, cheers, i'll give u the jist, basically the below code queries the database and sorts the bal_for_int_ban field and assigns it the correct letter for each record. See code below:




Select bal_amt, net_bal_for_prncpl, (
CASE
WHEN bal_for_int<0 THEN 'A'
WHEN bal_for_int > 0
And bal_for_int< 1000 THEN 'B'
WHEN bal_for_int > 1000.01
And bal_for_int< 5000 THEN 'C'
WHEN bal_for_int > 5000.01
And bal_for_int< 10000 THEN 'D'
WHEN bal_for_int > 10000.01
And bal_for_int< 15000 THEN 'E'
WHEN bal_for_int > 15000.01
And bal_for_int< 25000 THEN 'F'
WHEN bal_for_int > 25000.01
And bal_for_int< 50000 THEN 'G'
WHEN bal_for_int > 50000.01
And bal_for_int< 100000 THEN 'H'
WHEN bal_for_int > 100000.01
And bal_for_int< 1000000 THEN 'I'
ELSE 'J'
END) As bal_for_int_Band,




From filename.account_periodic_branch_acc
Where Load_Last_Action <>'D'
And period_dte='2006-03-31'
And Dr_Int_Cat Not In ( 47,48,49,51,52,54)
And Cr_Int_Cat <>499


Now what i am trying to do is fir my code or your code in so it pulls the records out with the valu of B in the bal_for_int_ban field and do the calculations for all these, calculation are net_margin average, balance average etc.
But alas it aint workin, any ideas how to join these in together. Do you get me


SELECT COUNT(*), AVG(NET_MARGIN), INT_RTE, SUM(BAL_AMT), SUM(NET_BAL_FOR_PRNCPL)
FROM filename.account_periodic_branch_acc
WHERE (Bal_for_int_ban = 'B')
AND (period_dte = '2006-03-31')
GROUP BY INT_RTE

Smoggy

Nope you lost me , does your table NOT have the "Bal_for_int_ban" field in it ? its a calculated field ?

empirix

it does have the field in it, basically it contains the values i.e. A, b, c etc, basicallt the first part of the code runs fine, its just my part where i want it to find all the records with a value of B in the bal_for_int_ban field and then get the other values in the select statement for this field

Smoggy

Nope, explain it in fools language , as what u have explained so far makes NO sense.

As the select with the case in it calculates the banding , but you say that this banding is already in your table, so why bother to recalculate it ?

Edit : If its a calculated field maybe you would be best of moving the case to be part of your where :

such as :

WHERE (Bal_for_int_ban =
CASE
WHEN bal_for_int<0 THEN 'A'
WHEN bal_for_int > 0
And bal_for_int< 1000 THEN 'B'
WHEN bal_for_int > 1000.01
And bal_for_int< 5000 THEN 'C'
WHEN bal_for_int > 5000.01
And bal_for_int< 10000 THEN 'D'
WHEN bal_for_int > 10000.01
And bal_for_int< 15000 THEN 'E'
WHEN bal_for_int > 15000.01
And bal_for_int< 25000 THEN 'F'
WHEN bal_for_int > 25000.01
And bal_for_int< 50000 THEN 'G'
WHEN bal_for_int > 50000.01
And bal_for_int< 100000 THEN 'H'
WHEN bal_for_int > 100000.01
And bal_for_int< 1000000 THEN 'I'
ELSE 'J'
END)
)

Hope this is of some help, as im really not clear where you are going with this.

empirix

Thanks smog,

amazing how refreshingly annoying it is to learn this again, reminds me of first year of college, although i probably knew more then than i do now, old age i tell ya. I remember when i...........