I have this query: SELECT avg(sum) FROM (     SELECT date_trunc('month', &quot;timestamp&quot;), sum(amount) as sum     FROM bookkeeping WHERE summary LIKE 'Bitcoin purchase:%'     GROUP BY date_trunc('month', &quot;timestamp&quot;) ) a  It gave me an impossible average cost of my Bitcoin purchases per month. I soon found out why: it ignores all the numerous months over the years where I did not buy Bitcoin at all, including only months when I did buy at least once. Since there aren't a ton of &quot;empty&quot; months included (with 0 for sum), it does the average of the sums for only the months where I bought at least once, so the average becomes completely wrong. How do I make it &quot;invent&quot; rows for the months which aren't represented by at least one record, so that the avg(sum) will actually be meaningful?
122494
Consider:  with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day )  select 'Inspections'::text as data_label, count(i.reporting_id) as daily_count, d.day as date_column from days d left join inspection i on i.close_case_date = d.day group by d.day order by d.day   This returns a set that looks like this:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    4           01/04/13 Inspections    8           01/06/13   Note the 1 and 2 day gaps in the record set. I need to generate a set with those values filled in with 0 like so:  data_label | daily_count | date_column Inspections    1           01/01/13 Inspections    2           01/02/13 Inspections    0           01/03/13 Inspections    4           01/04/13 Inspections    0           01/05/13 Inspections    8           01/06/13   How would I do this? I am not much of a Database Developer and am new to Postgres, so I am not sure where to start and can not find anything on how to accomplish this objective.
9755
for a few days I'm stuck at comparing two dates.  In a big table with dates as columns and products as rows, we want to display the amount of sales per day per product.  What the following query does:   Import our sales via IMPORTRANGE query the specific sales for a given day and product ID return 0 if there is an error (e.g. not found) count the result   This is a working query with the date 2018-03-26 hard coded:  =COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '2018-03-26'"; 0)))  It shows how many sales a specific product has on the specified date.  Now I want the date from a cell in the sheet:  =COUNTA(IFERROR(query(IMPORTRANGE("1OHR0nr_w3oFNNxtMxRwv65xncCBRpK-vV3vvqmHGFPo"; "Sales March!A:K"); "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '"&amp;$AR3&amp;"'"; 0)))  But this doesn't work. I tried A LOT of things for the past 3 days but I have no idea if I did that wrong (I'm a beginner) so I'm turning to you. What might be wrong with the date comparison in this section?  "select Col6 where Col6 = '"&amp;$A95&amp;"' and todate(Col11) = date '"&amp;$AR3&amp;"'"  You can see it  in Column AR Row 95 but I can't give edit rights.
51081
I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.  So let's say I have a table like this:  TransactionID  | Date ---------------|-----------              1 | 8-04-2014              2 | 8-05-2014              3 | 8-18-2014              4 | 8-18-2014              5 | 8-20-2014   I want to have a select statement that would provide me with something like  TotalTransactionsToDate | Week | Year ------------------------|------|------                       2 |    1 | 2014                       5 |    3 | 2014   I am using MySql 5.5.38
50188
So for the last few months I've been doing a lot of forecasting for my company and specifically I've been looking at monthly forecasts of total weight of different categories of products output's each month.  I've been using time series models such as Arima, ETS, and tslm() within R to do my forecasting, as well as using cross validation to select a model. Over the last two days I've been presenting my results and discussing implementation of my forecasts.  But, I've been asked the same question multiple times and I don't know the answer to it, so let me ask you guys.  If this has been asked before, I apologize.  I'll write out a few questions that hopefully will make clear what I'm trying to understand.  Also, I'd like to keep technical answers about the models in the context of R, since that's what I'm using.   Do time series models take into account the number of days in a month? Particularly, do time series models consider the number of business days in a month? (or is there a way to incorporate this?) Do we even need to worry about this when forecasting using a time series model or does the model account for this?   For instance, lets say in October of 2014 a certain category sold 35,000 lbs of a product, and that there were 31 days, 23 of which were business days.  Well for this year, 2015, there are still 31 days, but now there are only 22 business days.  Just some background on the data, I have monthly data that starts in August of 2008.    So would it possibly be better to average the weight per month with the number of business days and forecast out this way since the # of business days change month to month and year to year?
25277
I need an aggregate function that MySQL doesn't provide.  I would like it to be in MySQL's flavor of SQL (that is, not in C).  How do I do this?  What I'm stuck on is creating an aggregate function -- the  don't seem to mention how this is done.  Examples of desired usage of a product function:  mysql&gt; select product(col) as a from `table`; +------+ | a    | +------+ |  144 | +------+ 1 row in set (0.00 sec)  mysql&gt; select col, product(col) as a from `table` group by col; +-----+------+ | col | a    | +-----+------+ |   6 |   36 | |   4 |    4 | +-----+------+ 2 rows in set (0.01 sec)
103375
In Postgres 11, according to documentation, txid_current() should return biginteger but I'm getting small integer that doesn't look like is extended by epoch counter. My txid looks like this: 1123  "The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions  export a 64-bit format that is extended with an “epoch” counter so it will not wrap around during the life of an installation."     Am I interpreting documentation incorrectly?
112666
I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent lives_remaining total for each usr_id   There are multiple users (distinct usr_id's) time_stamp is not a unique identifier: sometimes user events (one by row in the table) will occur with the same time_stamp. trans_id is unique only for very small time ranges: over time it repeats remaining_lives (for a given user) can both increase and decrease over time   example:  time_stamp|lives_remaining|usr_id|trans_id -----------------------------------------   07:00  |       1       |   1  |   1       09:00  |       4       |   2  |   2       10:00  |       2       |   3  |   3       10:00  |       1       |   2  |   4       11:00  |       4       |   1  |   5       11:00  |       3       |   1  |   6       13:00  |       3       |   3  |   1       As I will need to access other columns of the row with the latest data for each given usr_id, I need a query that gives a result like this:  time_stamp|lives_remaining|usr_id|trans_id -----------------------------------------   11:00  |       3       |   1  |   6       10:00  |       1       |   2  |   4       13:00  |       3       |   3  |   1       As mentioned, each usr_id can gain or lose lives, and sometimes these timestamped events occur so close together that they have the same timestamp! Therefore this query won't work:  SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM        (SELECT usr_id, max(time_stamp) AS max_timestamp         FROM lives GROUP BY usr_id ORDER BY usr_id) a  JOIN lives b ON a.max_timestamp = b.time_stamp   Instead, I need to use both time_stamp (first) and trans_id (second) to identify the correct row. I also then need to pass that information from the subquery to the main query that will provide the data for the other columns of the appropriate rows. This is the hacked up query that I've gotten to work:  SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM        (SELECT usr_id, max(time_stamp || '*' || trans_id)         AS max_timestamp_transid        FROM lives GROUP BY usr_id ORDER BY usr_id) a  JOIN lives b ON a.max_timestamp_transid = b.time_stamp || '*' || b.trans_id  ORDER BY b.usr_id   Okay, so this works, but I don't like it. It requires a query within a query, a self join, and it seems to me that it could be much simpler by grabbing the row that MAX found to have the largest timestamp and trans_id. The table "lives" has tens of millions of rows to parse, so I'd like this query to be as fast and efficient as possible. I'm new to RDBM and Postgres in particular, so I know that I need to make effective use of the proper indexes. I'm a bit lost on how to optimize.  I found a similar discussion . Can I perform some type of Postgres equivalent to an Oracle analytic function?  Any advice on accessing related column information used by an aggregate function (like MAX), creating indexes, and creating better queries would be much appreciated!  P.S. You can use the following to create my example case:  create TABLE lives (time_stamp timestamp, lives_remaining integer,                      usr_id integer, trans_id integer); insert into lives values ('2000-01-01 07:00', 1, 1, 1); insert into lives values ('2000-01-01 09:00', 4, 2, 2); insert into lives values ('2000-01-01 10:00', 2, 3, 3); insert into lives values ('2000-01-01 10:00', 1, 2, 4); insert into lives values ('2000-01-01 11:00', 4, 1, 5); insert into lives values ('2000-01-01 11:00', 3, 1, 6); insert into lives values ('2000-01-01 13:00', 3, 3, 1);
102819
I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong.  I have two tables in my database; one is to store Students records another one is to store their attendance day wise  Now I want to display a report of all students in particular class for the current month whether they were present or absent. But I am capturing only Absent students details only in attendance table.  I have written sql query to display the result here is:  SELECT tab.class, attend, DATE, ta.rollno, ta.StdNm  FROM tbl_absentees tab, tbl_admission ta WHERE ta.Cls = class   AND ta.rollno = tab.rollno   AND class =22   AND attend =  'A'   AND DATE =  '2013-06-07';   The result is:  Class Attend RollNo StudentName   But I want to display in 31 days table manner by taking only Date in attendance table if attend=A display A for absent Days otherwise Display 'P' for remaining days  How can I do this in mysql? Can anyone suggest/give me an idea to achieve this.  Sorry for misclarification in my question. Actually I want to display an attendance report for a particular month where data comes from two tables:   the first table consists of StudentName, RollNo, Class the second table consists of Date, Status, RollNo, Class   Now I want to display report like .
101689
I have a table tabName with the following rows:  id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open) 1          2015-05-10 00:00:00   1970-01-01 ..       open 2          2015-05-10 ..         1970-01-01 ..       new 3          2015-05-10            2015-05-12          resolved 4          2015-05-11 ..         1970-01-01 ..       open 5          2015-05-11 ..         1970-01-01 ..       new 6          2015-05-11            2015-05-11          resolved 7          2015-05-12 ..         1970-01-01 ..       open 8          2015-05-12 ..         1970-01-01 ..       new 9          2015-05-12            2015-05-12          resolved   NOTE:  1970-01-01 .. is default time for all the tickets which are still open.  I have to frame a query to get the count of tickets still open, date wise. Say like on 10 May I have 3 tickets opened. Noting that for id = 3, resolved date is not 10 but it's closed on 12 May.  I framed a query like  SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1) FROM tabName WHERE DATE_FORMAT(Resolved, '%d-%m') &gt; DATE_FORMAT(Created, '%d-%m')       AND YEAR(Created) = '2015' GROUP BY createdd;   The above query gets all the tickets whose Resolved date is greater than the date which is considered for the count. This may count newer tickets as well. And also if the older tickets which are not resolved yet is not counted.  Is there any other way to frame the required query? I have been juggling with this query from last 3 days. Not able to find out still.  Updated: As suggested by Abhik, i have tried in sqlfiddle site.  Here is what i created:  CREATE TABLE test    (idint,createddatetime,resolveddatetime, status varchar(15), PRIMARY KEY (id));    INSERT INTO test       (id,'created','resolved',status`) VALUES       (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),    (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),    (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),    (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),    (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),    (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),    (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),    (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),    (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')    ;   `   When i run  ` select created_date, @open:= @open+open_ticket-resolved as open from(  select   date_format(created,'%d-%m') as created_date ,  sum( case when status='open' then 1 else 0 end )+  sum( case when status='new' then 1 else 0 end )+  sum( case when status='resolved' and ( date(resolved) > date(created) or  date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,  sum(case when status = 'resolved' and date(resolved) = date(created) then 1   else 0 end) as resolved  from test  where  YEAR(Created) = '2015'  group by created_date )x,  (select @open:=0)r order by created_date ; `  I got following output  created_date   open 10-05   3 11-05   5 12-05   8  Here count of still open tickets for 12-05 must be 7. The above query includes ticket id "3" , which is closed on 12-05. So is there any way to exclude this? If i am able to get this right, then i will have full fledged query.
44774
I have a table structured like this:  CREATE TABLE [TESTTABLE] (     [ID] [int] IDENTITY(1,1) NOT NULL,     [DateField] [datetime] NULL,     [StringField] [varchar](50),     [IntField] [int] NULL,     [BitField] [bit] NULL )   I execute the following code:  BEGIN     INSERT INTO TESTTABLE (IntField, BitField, StringField, DateField)     VALUES ('1', 1, 'hello', {ts '2009-04-03 15:41:27.378'});       SELECT SCOPE_IDENTITY()   END   And then   select * from testtable with (NOLOCK)   and my result shows:  2009-04-03 15:41:27.*377*   for the DateField column.  Any ideas why I seem to be losing a millisecond??
87281
This is the scenario  SQL&gt; exec dbms_stats.gather_table_stats(user,'TM', cascade=&gt;true)  PL/SQL procedure successfully completed.  SQL&gt; SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / 1024 MB , BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('TM', 'TM_LD_IX');  SEGMENT_NAME            SEGMENT_TYPE        MB        BLOCKS ------------------------------------------ ---------- ---------- TM                      TABLE                 296      37888 TM_LD_IX                INDEX                  46       5888  SQL&gt; select index_name , column_name from user_ind_columns where index_name = 'TM_LD_IX';  INDEX_NAME   COLUMN_NAME ------------ ------------------------------ TM_LD_IX     LD  SQL&gt; explain plan for select distinct LD from TM;  Explained.  SQL&gt; @ex  PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------  Plan hash value: 4241255022  -------------------------------------------------------------------------------------- | Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 | |   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 | |   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 | --------------------------------------------------------------------------------------  9 rows selected.  SQL&gt; explain plan for select /*+ index(x , TM_LD_IX) */ distinct LD from TM x;  Explained.  SQL&gt; @ex  PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------  Plan hash value: 4241255022  -------------------------------------------------------------------------------------- | Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 | |   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 | |   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 | --------------------------------------------------------------------------------------  SQL&gt; select * from v$version;  BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE    10.2.0.3.0      Production TNS for 32-bit Windows: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production   As you can see oracle is not using the index on LD and chooses a full table scan instead. I can't even make him use the index with a hist.   In the simple query above I would expect an index fast full scan of TM_LD_IX. my db_file_multiblock_read_count is set to 32 so i'm expecting a cost of about 5888 / 32 = 184 (using the index I can also save the cost of a hash unique).  So, what am I missing here ?
119235
I'm trying to use an aggregate query, but one of the fields I want to group by is not groupable (saw it in the soql error and then verified groupable=false in the metadata).  It's just a number field that holds an integer (number 2,0 in salesforce field spec-speak).  Is there a trick to make it groupable?  Any way around this problem? I don't want to write hundreds of lines of code just to average a list by this field.
8133
I'm trying to use MySQL to create a view with the "WITH" clause  WITH authorRating(aname, rating) AS    SELECT aname, AVG(quantity)    FROM book    GROUP BY aname   But it doesn't seem like MySQL supports this.  I thought this was pretty standard and I'm sure Oracle supports this. Is there anyway to force MySQL to use the "WITH" clause?  I've tried it with the MyISAM and innoDB engine.  Both of these don't work.
21450
Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases  (PIVOT, ROWS to COLUMNS, TRANSPOSE &amp;c.) and all I get is  TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.  I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.  stock_date       sku ----------       --- 2016-06-01         0 2016-06-02       123 2016-06-03       123 2016-06-04       123 2016-06-06       123 2016-06-07       123 2016-06-03       245 &lt;... &amp;c...&gt;   There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.   So, I want to make the stock_date field a column.   sku     1   2   3   4   5   6   7 ---     -   -   -   -   -   -   -  123     0   Y   Y   Y   0   Y   Y 245     0   0   Y.... &amp;c.   Ideally, the code would be dynamic and would work with any server (even MySQL).   But if it has to use common table expressions and/or window functions, that's  OK but not proprietary crosstab or pivot functions.
65979
I know I need to have (although I don't know why) a GROUP BY clause on the end of a SQL query that uses any aggregate functions like count, sum, avg, etc:  SELECT count(userID), userName FROM users GROUP BY userName   When else would GROUP BY be useful, and what are the performance ramifications?
74540
I have a query pattern that must be very common, but I don't know how to write an efficient query for it.  I want to look up the rows of a table that correspond to "the most recent date not after" the rows of another table.  I have a table, inventory say, which represents the inventory I hold on a certain day.  date       | good | quantity ------------------------------ 2013-08-09 | egg  | 5 2013-08-09 | pear | 7 2013-08-02 | egg  | 1 2013-08-02 | pear | 2   and a table, "price" say, which holds the price of a good on a given day  date       | good | price -------------------------- 2013-08-07 | egg  | 120 2013-08-06 | pear | 200 2013-08-01 | egg  | 110 2013-07-30 | pear | 220   How can I efficiently get the "most recent" price for each row of the inventory table, i.e.  date       | pricing date | good | quantity | price ---------------------------------------------------- 2013-08-09 | 2013-08-07   | egg  | 5        | 120 2013-08-09 | 2013-08-06   | pear | 7        | 200 2013-08-02 | 2013-08-01   | egg  | 1        | 110 2013-08-02 | 2013-07-30   | pear | 2        | 220   I know one way of doing this:  select inventory.date, max(price.date) as pricing_date, good from inventory, price where inventory.date &gt;= price.date and inventory.good = price.good group by inventory.date, good   and then join this query again to inventory. For large tables even doing the first query (without joining again to inventory) is very slow.  However, the same problem is quickly solved if I simply use my programming language to issue one max(price.date) ... where price.date &lt;= date_of_interest ... order by price.date desc limit 1 query for each date_of_interest from the inventory table, so I know there is no computational impediment.  I would, however, prefer to solve the whole problem with a single SQL query, because it would allow me to do further SQL processing on the result of the query.  Is there a standard way to do this efficiently? It feels like it must come up often and that there should be a way to write a fast query for it.  I'm using Postgres, but an SQL-generic answer would be appreciated.
88212
This question was inspired by  [closed] and is virtually identical to this  but using different RDBMS's (PostgreSQL vs. MySQL).  Suppose I have a list of tumours (this data is simulated from real data):  CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);  INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40'); INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40'); INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40'); INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40'); INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40'); INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32'); INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32'); -- 2017, with 1 Cervix and Lung each for the month of Jan - tie! INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40'); INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32'); INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32'); INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');   You want to find out which particular tumour was most common in a given month - so far so good!    Now, you will notice that for month 1 of 2017, there is a tie - so it makes no sense whatsoever to randomly pick one and give that as the answer - so ties have to be included - this makes the problem much more challenging.   The correct answer is:    Year    Month  Tumour count      Type   2017        1             1    Cervix  -- note tie   2017        1             1      Lung  --   "   "   2017        2             3      Lung   2018        1             5    Cervix   2018        2             3      Lung   A further bonus would be to have the month name appear as text rather than an integer.  I have a solution but it's quite complex - I'd like to know if my solution is optimal or not. The MySQL fiddle is !