Calculating (dividing) two summed fields in SQL Query -


i have query:

select fmum.dimsubscriptionkey, sum(fmum.totalunits) totalunits, sum(fmum.ai_normalizedusage) ai_normalizedusage   [ai_datamart].[azureviews].[v_factmeteredusagemonthly] fmum join [ai_datamart].[azureviews].[v_dimserviceextended] se on(fmum.dimservicekey = se.dimservicekey) join [ai_datamart].[azureviews].[v_dimaccount] on(fmum.dimaccountkey = a.dimaccountkey) join [ai_datamart].[azureviews].[v_dimsubscription] su on(fmum.dimsubscriptionkey = su.dimsubscriptionkey)   fmum.dimdatekey >= '20150201'and fmum.dimdatekey <= '20150331' , se.workload = 'sqldb' , se.resourcename != 'sql reporting hours' , (su.ai_billingtype = 'ea' or su.ai_billingtype = 'direct')   , su.isfraudidentified = 0 , su.ai_istest = 0 , su.dimbillingsystemkey = 1 , fmum.dimsubscriptionkey = '4707785' group fmum.dimsubscriptionkey

which returns result:

dimsubscriptionkey totalunits ai_normalizedusage 4707785 24.77043700 21.08775630

what need , can't work 4th column (ai_normaliedusage / totalunits)

but when add query:

select fmum.dimsubscriptionkey, sum(fmum.totalunits) totalunits, sum(fmum.ai_normalizedusage) ai_normalizedusage, (ai_normalizedusage / totalunits)   [ai_datamart].[azureviews].[v_factmeteredusagemonthly] fmum join [ai_datamart].[azureviews].[v_dimserviceextended] se on(fmum.dimservicekey = se.dimservicekey) join [ai_datamart].[azureviews].[v_dimaccount] on(fmum.dimaccountkey = a.dimaccountkey) join [ai_datamart].[azureviews].[v_dimsubscription] su on(fmum.dimsubscriptionkey = su.dimsubscriptionkey)   fmum.dimdatekey >= '20150201'and fmum.dimdatekey <= '20150331' , se.workload = 'sqldb' , se.resourcename != 'sql reporting hours' , (su.ai_billingtype = 'ea' or su.ai_billingtype = 'direct')   , su.isfraudidentified = 0 , su.ai_istest = 0 , su.dimbillingsystemkey = 1 , fmum.dimsubscriptionkey = '4707785' group fmum.dimsubscriptionkey, (ai_normalizedusage / totalunits)

i not getting expected results:

dimsubscriptionkey  totalunits  ai_normalizedusage  (no column name) 4707785 1.45831000  0.70567620  0.48389999382847268413 4707785 3.39577900  3.28609533  0.96769999755578911348 4707785 9.49984800  9.19300290  0.96769999898945751553 4707785 3.41661200  3.30625543  0.96769999929754973640 4707785 4.49992800  2.17751515  0.48389999795552284392 4707785 2.49996000  2.41921129  0.96769999919998719979

what want is

dimsubscriptionkey totalunits ai_normalizedusge newcalculatedfield<br> 4707785 24.770437   21.0877563  0.851327585

what missing? racking brain!

if you're referencing derived columns within same select statement can't refer them alias, need either duplicate calculation, or wrap in select statement.

without knowing underlying tables, fix should work:

select fmum.dimsubscriptionkey, sum(fmum.totalunits) totalunits, sum(fmum.ai_normalizedusage) ai_normalizedusage, sum(fmum.ai_normalizedusage) / sum(fmum.totalunits) newcalculatedfield   [ai_datamart].[azureviews].[v_factmeteredusagemonthly] fmum join [ai_datamart].[azureviews].[v_dimserviceextended] se on(fmum.dimservicekey = se.dimservicekey) join [ai_datamart].[azureviews].[v_dimaccount] on(fmum.dimaccountkey = a.dimaccountkey) join [ai_datamart].[azureviews].[v_dimsubscription] su on(fmum.dimsubscriptionkey = su.dimsubscriptionkey)   fmum.dimdatekey >= '20150201'and fmum.dimdatekey <= '20150331' , se.workload = 'sqldb' , se.resourcename != 'sql reporting hours' , (su.ai_billingtype = 'ea' or su.ai_billingtype = 'direct')   , su.isfraudidentified = 0 , su.ai_istest = 0 , su.dimbillingsystemkey = 1 , fmum.dimsubscriptionkey = '4707785' group fmum.dimsubscriptionkey

Comments

Post a Comment

Popular posts from this blog

java - Spring Data JPA: Why findOne(id) executing delete query internally? -

i have 1 application using @manytomany annotation link 2 different entities 1 parent entity. not sure if have implemented properly. when execute .findone() on parent entity's repository find it, @ first fetch data along executing delete queries internally delete record mapping table (a table jpa creates after adding @manytomany annotation.). following generated sql queries , code snippet: java class @entity @table(name = "inventory") public class inventory { @id long id; // many other properties @manytomany(fetch=fetchtype.lazy) private list<stream> streams; @manytomany(fetch=fetchtype.lazy) private list<subject> subjects; } there no other annotation i've put in stream , subject entity class link inventory class. when run app, create following tables: inventory stream subject inventory_stream inventory_subject (inventory_stream , inventory_subject, 2 tables created automatically, don't k...
Read more

python - Mongodb How to add addtional information when aggregating? -

i beginner , wrote line pipeline works, wanna add other information output, screen name , or number of tweets.i tried add under $group gave me syntax error everytime here pipeline: def make_pipeline(): # complete aggregation pipeline pipeline = [ { '$match': { "user.statuses_count": {"$gt":99 }, "user.time_zone": "brasilia" } }, { "$group": { "_id": "$user.id", "followers": { "$max": "$user.followers_count" } } }, { "$sort": { "followers": -1 } }, { "$limit" : 1 } ]; i using on example : { "_id" : objectid("5304e2e3cc9e684aa98bef97"), "text" : "first week of school on :p", "in_reply_to_status_id" : null, "retweet_count" : null, ...
Read more

java - Incorrect order of records in M-M relationship in hibernate -

Image
i have 4 records in bridge table in mysql database. using @manytomany in hibernate, accurate order of employees incorrect incorrect of projects. following required material figure out doing mistake. this source. @entity @table(name="project") public class projectbean { @id @generatedvalue @column(name="project_id") private int projectid; @column(name="title") private string projecttitle; @manytomany(mappedby="projects") private collection<employeebean> employees; public projectbean() { employees = new arraylist<employeebean>(); } //getters & setters } @entity @table(name="employee") public class employeebean { @id @generatedvalue @column(name="employee_id") private int employee_id; @column(name="first_name") private string ...
Read more