sql - Getting average per day using the extract and last_day functions -

-

Here's a quick help, I have a small project here, I have this report with the date of total order Order (this will actually continue with my second question) So what am I doing here 

  SELECT sf.ORDER_QNT, dd.ACTUAL_DATE, dd.MONTH_NUMBER to sales_fact SF, date_dim dd WHERE Dd .date_id = sf.date_id and dd.MONTH_NUMBER = 1; ORDER_QNT ACTUAL_DATE MONTH_NUMBER ------------------------------------------- 1100 05/01 / 13 1 100 05/01/13 1 140 06/01/13 1 110 07/01/13 1 200 08/01/13 1 500 08/01/13 1 230 08/01/13 1 500 08/01 / 13 1 200 08/01/13 1 53 15/01/13 1 53 22/01/13 1   
 Now, I want to get the average of that month (average per day) Using the Last_day function.  
  SELECT sum (sf.ORDER_QNT) / extract (DAY FROM LAST_DAY (to_date ('05 / 01/13 ',' dd / mm / rr '))) as AVGPERDAY, dd .month_number FROM sales_reference sf, date_dim dd WHERE dd.date_id = sf.date_id and dd.month_number = 1 GROUP dd.month_number; AVGPERDAY MONTH_NUMBER -------------------------- 113.785714 1   
 The result is good, but now when I date Replace with DD.activ_det, this error returns  
  SELECT sum (sf.ORDER_QNT) / extract (day to LAST_DAY (DD. Active_date)) as AVGPERDAY, dd.month_number FROM sales_fact sf, date_dim dd WHERE dd.date_id = sf.date_id and dd.month_number = 1 GROUP dd.month_number; Error in command line: 1 column: 53 error report - SQL error: ORA-009798: Group does not contain expression 00 9 79. 00000 - "no group with expression"   
 Can anyone help?   
 
 
  sum (sf.ORDER_QNT) / decode (round (max (dd.actual_date) Minimum (dd.actual_date)), 0, 1)   
 You can use this expression in your query (but the result can not be the same with LAST_DAY)  
 Or you can extend the part given by a group (DAY FROM LAST_DAY (DD.ActivateDet)):  
  SELECT sum (sf.ORDER_QNT) / extract (DAY FROM LAST_DAY) (dd.actual_date))) as AVGPERDAY, dd.month_number FROM Sales_Fact SF, date_dim dd WHERE dd.date_id = sf.date_id and dd.m Onth_number = 1 GROUP dd.month_number, extract (DAY FROM LAST_DAY (dd.actual_date));

Comments

Post a Comment

Popular posts from this blog

php - PDO bindParam() fatal error -

-
I am trying to learn PDO, now I have written this small code, but it gives me a serious error: Fatal error: Call a member function at a non-object dot () ... $ con = new mysqli ("127.0.0.1", "root", "", "csvdangercheck"); $ Query = $ con- & gt; ("Id,` var1`, `var2`) values ​​(" id,: var1,: var2) ";); $ query- & gt; bypass pattern (': id', $ id); $ query- & gt; BindParam (': var1', $ VAL1); $ Query- & gt; bindParam (': var2', $ val2); $ Query-> Executed (); I Trying to use print_r ($ con-> errorInfo ()) but Fatal error: Undefined method mysqli :: errorInfo ( ) ... can someone tell me what am I missing? Flaffeh said, you have PDO Missing Mysqy, try it: $ con = new PDO ('mysql: host = 127.0.0.1; dbname = csvdangercheck', 'root', '');
Read more

php - How can I cram 6+31 numeric characters into 22 alphanumeric characters? -

-
I have a 6 digit number and a 31-digit number (such as "234536" and "201103231043330478311223582826") that I The API needs to be crawled in the same 22-character alphanumeric field that uses PHP. I tried to convert each one to 32 (using custom code could not handle the base_convert () large number) and to connect with a single-character delimiter, but It only gives me 26 characters This is a restore API, so the letters should be URI-protected. I would like to do a database table cross with two references without reference to another reference value, if possible, any suggestions? Instead use a radix of 62. This will give you 22 characters for the top, 3.35 for the pre and 17.3 for the next. & gt; & Gt; & Gt; Math.log (10 ** 6) /math.log (62) 3.3474826039165504 & gt; & Gt; & Gt; Math.log (10 ** 31) /math.log (62) 17.295326786902177
Read more

logging - How can I log both the Request.InputStream and Response.OutputStream traffic in my ASP.NET MVC3 Application for specific Actions? -

-
For a specific set of tasks, I need to log in to the incoming request inputstream as well as outgoing response. is. I am using an ActionFilterAttribute for this and overriding the OnActionExecuted and OnResultExecuted methods. So I'm starting with this idea ... Public category ActionLoggerAttribute: ActionFilterArrbit {Public Override Zero On-Offed Execution (ActionActioned Contact Filter Filter) {base.OnActionAxecuted (FilterContext); HttpRequestBase request = filterContext.HttpContext.Request; // TODO: Log the request. InputStream} Public Override Zero OnResultExecuted (ResultExecutedContext FilterContext) {base.OnResultExecuted (filterContext); HTTPRPSPointBase Response = FilterContacts Hpptex response; // TODO: Log on Response.OutputStream}} Ideally I'll hook it up with enterprise library logging because I'm already using it for error logging. Am I reaching the input and output stream at the right time? Can I easily use the Enterprise Library to log i...
Read more