I have a table that looks like this in Google Big Query:      custID  InteractionDate Purchased?  Sales     1       20150312           F         0     1       20150312           T         200     1       20150314           T         150     2       20150221           T         400     2       20150221           F         0     2       20150221           T         120   ..that I need to transform to the following structure:       custID InteractionDate Success Sales      1     20150312          0.5    200      1     20150314           1     150      2     20150221         0.66    520   ..where in this second table the success column is defined as               count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),   and sales is just the sum.  I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.
122132
I have a table that looks like this in Google Big Query:  custID  InteractionDate Purchased?  Sales 1       20150312           F         0 1       20150312           T         200 1       20150314           T         150 2       20150221           T         400 2       20150221           F         0 2       20150221           T         120   ..that I need to transform to the following structure:   custID InteractionDate Success Sales  1     20150312          0.5    200  1     20150314           1     150  2     20150221         0.66    520   ..where in this second table the success column is defined as  count(purchase=Yes)/(count(purchase=Yes) + count(purchase=No)),   and sales is just the sum.  I'm quite new to BQ and no-SQL languages. I simply don't know how to do this, I've searched and seen examples that suggest using partition over but I simply haven't seen any example that does just this. Thanks a lot in advance.
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.
20669
I have a table, showing the status (healthy, broken) for each device at a given timestamp. I need to get the total amount of time of a whole breakdown.  So the data is stored like this:  device_owner   device_id    timestamp          status owner1         device_1     2001-01-01 09:00   0 owner1         device_2     2001-01-01 09:15   0 owner1         device_1     2001-01-01 09:30   1 owner1         device_2     2001-01-01 09:45   1   and so on.  The example above shows    device_1 was down from 09:00 to 09:30 (total 30 mins),   device_2 was down from 09:15 to 09:45 (total 30 mins)   What I need  Need to calculate the total amount of time for the whole breakdown for owner_1 will be 45 mins (from 09:00 to 09:45) instead of 60 mins (30 + 30).  device_owner   total_breakdown_min owner_1        45   What I've done  Currently I can define a breakdown time for each device separately (device_1 = 30mins, device_2 = 30mins). For this I transformed the table using LEAD over partition by device_owner, device_id and date part from timestamp:  device_owner  device_id     timestamp        status lead_timestamp   lead_status owner1        device_1     2001-01-01 09:00   0     2001-01-01 09:30     1 owner1        device_1     2001-01-01 09:30   1     NULL                 NULL owner1        device_2     2001-01-01 09:15   0     2001-01-01 09:45     1 owner1        device_2     2001-01-01 09:45   1     NULL                 NULL   So the datetime difference between timestamp and lead_timestamp in sum gives me the total breakdown time for a device.  ;WITH LeadStatus AS (     SELECT         D.*,         lead_status = LEAD(D.status) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC),         lead_timestamp = LEAD(D.datetime) OVER (PARTITION BY D.device_owner, D.device_id, D.date ORDER BY D.datetime ASC)     FROM         #DeviceStatus AS D ) SELECT * FROM LeadStatus   It is the same device list for any owner. The data snapshot is to be taken between 2 dates (StartDate, EndDate). The records are written to a DB each n seconds and also when the device status changes.
19543
My data looks like this:  Parameter   Location_A   Location_B   Location_C  Location_D  A           1            0.3          0.2         0.1 B           0.9          0.3          0.1         0.1 C           1.1          0.2          0.3         0.2   I have 365 parameters and 768 locations.  I want to create one row for each parameter and location combination and show the results in a third column (i.e., 365*768 = 280,320):  Location       Parameter     Result Location_A     A             1 Location_A     B             0.9 Location_A     c             1.1 Location_B     A             0.3 Location_B     B             0.3   And so on.  Is there an easy way to do this?  I have a header row and then 365 rows for each parameters and column B thru ACO are locations.  I've looked through a few things but cannot seem to find the answer:
77188
I have a spreadsheet of my bank account transaction history in this format:    | A          | B        | C        | D 1 | Date       | Amount   | Balance  | Category 2 | 2013-03-10 | $ -10.59 | $3700.76 | Food shopping 3 | 2013-03-11 | $ - 5.10 | $3695.66 | Car parking 4 | 2013-03-11 | $ 100.00 | $4695.66 | Salary   In my spreadsheet the Balance column is hidden because I recalculate it myself.  I want to have a summary for each month with data looking like this:    | A       | B        | C           | D 1 | Month   | Income   | Expenditure | Net 2 | 2013-03 | $ 100.00 |     $ 15.69 | $ 84.31 3 | 2013-04 | $  00.00 |     $ 00.00 | $ 00.00   These are the formulas I have right now, so I can compute Net fine, but I don't know how to compute Income or Expenditure on a monthly basis because SUMIF only lets me do criteria on a single column (in this case, the Date column, but I want to only sum rows &gt;0.0 for Income and &lt;0.0 for Expenditure):  Net = =SUMIF( MonthAndYear( Txn!$A:$A ) , $A$2, Txn!$B:$B )
80779
How to 'group by' a query using an alias, for example:  select count(*), (select * from....) as alias_column  from table  group by alias_column   I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query?
93807
I have an SQL 2005 table, let's call it Orders, in the format:  OrderID, OrderDate,  OrderAmount 1,       25/11/2008, 10 2,       25/11/2008, 2 3,       30/1002008, 5   Then I need to produce a report table showing the ordered amount on each day in the last 7 days:  Day,        OrderCount, OrderAmount 25/11/2008, 2,          12 26/11/2008, 0,          0  27/11/2008, 0,          0  28/11/2008, 0,          0 29/11/2008, 0,          0 30/11/2008, 1,          5   The SQL query that would normally produce this:  select count(*), sum(OrderAmount)     from Orders     where OrderDate&gt;getdate()-7     group by datepart(day,OrderDate)   Has a problem in that it will skip the days where there are no orders:  Day,        OrderCount, OrderAmount 25/11/2008, 2,          12 30/11/2008, 1,          5   Normally I would fix this using a tally table and outer join against rows there, but I'm really looking for a simpler or more efficient solution for this. It seems like such a common requirement for a report query that some elegant solution should be available for this already.   So: 1. Can this result be obtain from a simple query without using tally tables?  and 2. If no, can we create this tally table (reliably) on the fly (I can create a tally table using CTE but recursion stack limits me to 100 rows)?
61514
(Follow up from this post: )    PostgreSQL 9.5.  I can't divulge full details, but table has 22 columns and 5 indexes:   primary key ('pk'), text (btree) another text (btree) a timestamp with time zone (btree) a tsvector (gin) my latest one, a bigint (btree)   (From the previous post you know that I was trying to avoid creating this additional column, and just use an expression index--adding two integer columns together--without success.  The bigint column here probably could have been just 'integer' but I made a mistake creating it; it took about an hour to add the column, populate it, and reindex, so I'm hoping this isn't relevant but mentioning it just in case it is.)  All are btree except the tsvector.  The following queries all take only 12ms and use just an Index Scan:   SELECT pk FROM table ORDER BY pk DESC LIMIT 10 SELECT pk FROM table ORDER BY text_column DESC LIMIT 10 SELECT pk FROM table ORDER BY timestamp_column DESC LIMIT 10   But if I try to use my new bigint index for ORDER BY:  SELECT pk FROM table ORDER BY bigint_column DESC LIMIT 10  ...it takes 2.7s and uses Limit -&gt; Sort -&gt; Seq Scan.  My "cheating" method is the closest I seem to be able to get to making use of the index:  SELECT pk FROM table WHERE bigint_column &gt; 1000000 ORDER BY bigint_column DESC LIMIT 10   This takes 12ms and uses Limit -&gt; Sort -&gt; Bitmap Heap Scan (bigint_column &gt; 1000000) -&gt; Bitmap Index Scan (bigint_column &gt; 1000000).  This is after a VACUUM ANALYZE after adding the index.  I thought it strange that my expression index was not being used in the other question.  Now it's just a plain old column (I haven't even added what would be the necessary triggers to actually go this route.)  Why isn't my newest index being used, when the other three work "just fine"?  (As pointed out in the comments at  , an Index-Only Scan would be even better.  I fail to see why all of these queries wouldn't use at least an Index Scan, let alone an Index-Only Scan, instead of a full Seq Scan.)  The index definition has DESC NULLS LAST (although it's a non-nullable column.)
107704
I have data like multiples of 40. 40 80 120 160 200 and so on.    I would like to label every record that is a multiple of 100.  I have tried a couple of methods and can't get them to work either as a query in the label or as a select statement.  "STATION" =  %00   RIGHT ("STATION", 2) =  00     I get invalid SQL statement. Does anyone see what I am doing wrong?    Respectfully I did search and did not see any of the example questions you presented.  However it still does not answer my question, re-stated, what am I doing wrong?
89157
The following query will display all Dewey Decimal numbers that have been duplicated in the "book" table:  SELECT dewey_number,   COUNT(dewey_number) AS NumOccurrences FROM book GROUP BY dewey_number HAVING ( COUNT(dewey_number) &gt; 1 )   However, what I'd like to do is have my query display the name of the authors associated with the duplicated entry (the "book" table and "author" table are connected by "author_id").  In other words, the query above would yield the following:  dewey_number | NumOccurrences ------------------------------ 5000         | 2 9090         | 3   What I'd like the results to display is something similar to the following:  author_last_name | dewey_number | NumOccurrences ------------------------------------------------- Smith            | 5000         | 2 Jones            | 5000         | 2 Jackson          | 9090         | 3 Johnson          | 9090         | 3 Jeffers          | 9090         | 3   Any help you can provide is greatly appreciated.  And, in case it comes into play, I'm using a Postgresql DB.  UPDATE: Please note that "author_last_name" is not in the "book" table.
88523
I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.  this will be a little long, be patient please.  First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.  Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).  What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.  Structure of lotteryTBL:  ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew  1       5    6     8    15      18    25     08-21-2015         0 2       7    15    18   30      40    45     08-21-2015         0   Structure of resultsTBL:  ID    num1  num2  num3  num4  num5  num6     draw_date   1      15    18    35    38    40    47     08-21-2015    Now, here is the pseudocode of what i want:  after insert a new row into resultsTBL    total &lt;- 0    for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date        total &lt;- calculate how many of them are the same with the resultsTBL's row.    update lotteryTBL set howmanyknew = total where "some condition"   First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.  Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?
70835
I have some Excel columns that look like this.  1774    David                    Not Upheld 1770    James                    Upheld 1771    David                    Upheld 1772    Sam                      Upheld 1768    Arthur                   Not Upheld 1769    James                    Upheld   How can I write an Excel formula for: count the number of rows with 'James' in column B, and 'Upheld' in column C?  I know how to do each of these conditions individually (using COUNTIF), but I'm not sure of the best way to combine them.  Thanks!
55577
I'm trying to create a GDocs spreadsheet to collate data from a survey. I'm fairly new to the advanced features of GDocs/Excel but here's what I'd like to achieve. I've tried Googling but it's a tough thing to search.  I currently have a COUNTIF formula that looks like this:  COUNTIF( 'Survey Data'!F2:F200; "Divisional Exec" )   This works fine. But I would like to refine that. So I want to count the number of times a string appears when COLUMN A = 'ACT' and when COLUMN F = 'Divisional Exec' in the same row.  Is this possible to do in Google Spreadsheets? If so how do I do it?  I have tried  but can't seem to get it to work for me.
120564
I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.  my input Data    ONAY_ID  sip_RECno    KULLANICI       ONAY    1           1         user1          true    2           1         user2          true    3           1         user3          false    4           1         user4          true   Output would be   sip_RECno  user1     user2    user3     user4           1          true     true    false    true
62521
To be specific, I have an event table that logs when a user joins or leaves a team.  It looks something like the following:  ------------------------------------- | user | event  | team | timestamp | ------------------------------------- | A    | joined | 1    | 2016-1-1  | | B    | joined | 1    | 2016-1-1  | | C    | left   | 1    | 2016-1-1  | | C    | joined | 2    | 2016-1-1  | | A    | left   | 1    | 2016-1-2  | | A    | joined | 2    | 2016-1-2  | | B    | left   | 1    | 2016-1-3  | | A    | left   | 2    | 2016-1-3  | -------------------------------------   I need to restructure this in a view to look something like the following  -------------------------------------- | user | team | joined    | left     | -------------------------------------- | A    | 1    | 2016-1-1  | 2016-1-2 | | A    | 2    | 2016-1-2  | 2016-1-3 | | B    | 1    | 2016-1-1  | 2016-1-3 | | C    | 1    | null      | 2016-1-1 | | C    | 2    | 2016-1-1  | null     | --------------------------------------   How can I achieve this?  For more detail, I'm attempting to do this in Amazon Redshift (PostgreSQL)
64186
In my company, I need to join table A to table B:   Table A is the sources table  Table B is the empty table   I want to know that how can I do it and the result like table B?  Source table A:  ID    date     money    HOUR ----------------------------- 1     20-1      10       1 1     20-1      20       2 1     20-1      30       3 1     20-1      40       4 1     21-1      50       1 1     21-1      10       2 1     21-1      20       3 1     21-1      30       4 2     20-1      40       1 2     20-1      50       2 2     20-1      10       3 2     20-1      20       4   Empty table B:  ID   date   Hour1   Hour2   Hour3   Hour4    Sum ------------------------------------------------- 1    20-1    10      20      30      40      100 1    21-1    50      10      20      30      110 2    20-1    40      50      10      20      120
45653
I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row.  My initial sheet looks like this:    I need to do the following to optimize this sheet:   split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row. duplicate the data from all other cells on the original row (i.e. from columns A &amp; C:E), so that each new row contains the full data for each business I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps   The sheet should then look like this:    Using =TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!