group by - MySQL Pivot with substitutions

I am trying to take entries from a Forms Plugin and reformat the results into a more usable format for front end viewing. I believe that I can do this with a pivot but I can not figure it out from the examples provided. As a Side note the datatbase includes entries from a number of different forms so I meed to be able to identify only the field_id values that are important for the query.Current Data format| entry-id | field_id | slug | value || 1 | fld_123 | First | Bob || 1 | fld_234 | Last | Smith || 1 | ...Read more

mysql - Using group by to return a row with max()

I am trying to get the items that their item_id exists in an array (arr_items) and have the higher num.player_id | item_id | num | unique_number-----------------------------------------10 | 1 | 1 | 110 | 1 | 11 | 210 | 1 | 93 | 310 | 2 | 24 | 410 | 2 | 40 | 5The expected result is to get the item_id 1 with num 93, and item_id 2 with num 40.The below query returns no results. If I remove the max in the num, it works but doesn't return the item_id with...Read more

mysql - Join three tables with group by giving error

There are three tables in the database users, organization_entries and user_invoices and I am trying to Join these three tables and my query is somewhat like this select users.id , sum(user_invoices.due_amount) , organization_entries.id, organization_entries.createdAt from users INNER JOIN user_invoices ON users.id = user_invoices.customer_id INNER JOIN on users.id = organization_entries.user_id GROUP BY users.id ORDER BY organization_entries.createdAt;But again and again, I am getting this error - ERROR 1064 (42000): You have an error in you...Read more

mysql - How do I use VBA to flatten a table in Excel where data is split between rows?

I currently have a raw data table in Excel that summarizes the status of Phases A, B, and C for a given Engagement. Some Engagements may not have data for all 3 phases.Row| EngagementID | A_date | A_status | B_date | B_status | C_date | C_status1 | 201 | 2/2 | Approved | | | | 2 | 201 | | | 3/5 | Approved | | 3 | 201 | | | | | 4/1 | Pending 4 | 203 | 2/12 | Submitted| | | | ...Read more

mysql - Deploying to Heroku Error. Giving logs

http://gentle-wildwood-7307.herokuapp.com/App is deployed from C9 (mysql databases, NOT postgresql) and here are my logs. Unsure of what the issue is. 4.236.208.24" dyno= connect= service= status=503 bytes= 2014-11-16T17:41:31.091593+00:00 heroku[router]: at=error code=H10 desc="App crashed" method=GET path="/favicon.ico" host=gentle-wildwood-7307.herokuapp.com request_id=cc817f43-c067-47f5-b488-0e96f5207c2c fwd="64.236.208.24" dyno= connect= service= status=503 bytes= 2014-11-16T17:41:31.891130+00:00 heroku[router]: at=error code=H10 desc="...Read more

mysql - "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either: ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced. Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?...Read more

sql - Can I concatenate multiple MySQL rows into one field?

Using MySQL, I can do something like:SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;My Output:shoppingfishingcodingbut instead I just want 1 row, 1 col:Expected Output:shopping, fishing, codingThe reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?...Read more

mysql - How to change table schema when importing data with Hadoop Sqoop to Hive?

When importing data from MySQL to Hadoop Hive I need to add additional 'timestamp' field to a new table that Hive creates.Input: MySQL table fields : Name, e-nmail, addressOutput: Hive table fileds : Name, e-nmail, address, timestampQuestions:How to request Sqoop to add a 'timestamp' field to new table that itcreates to store imported data in Hive? How to modify schema (add / delete fields) of the existing table in Hive?...Read more

mysql - Using sqoop incremental update for updating Hive table

I am trying to update a hive table, based on the records from a mysql table.mysql-table: (table name: delimiter_test)+---------------+-----------------+| department_id | department_name |+---------------+-----------------+| 2 | Fitness || 3 | Footwear || 4 | Apparel || 5 | Golf || 6 | Outdoors || 7 | Fan Shop || 8 | Test |+---------------+-----------------+hive-table (table name: my_test)2 Fitness3 F...Read more

mysql - sqoop date to impala timestamp

I am importing data with sqoop from MySQL to hdfs as parquet file where it is used by Impala. There is a problem with conversion of MySQL DATE type to Impala TIMESTAMP. Impala error message when executing compute stats table or select * is:File 'hdfs://....parquet'has an incompatible type with the table schema for column 'day'.Expected type: INT32. Actual type: INT64Changing data type of the day column to BIGINT or STRING leaves the error msg same.Even when I change day column type in Impala to STRING and set --map-column-java "day=String" (al...Read more

mysql - Select data from one table and insert into another existing table, which doesn't exist in the table

My table scheme is as follows: (Bold column name is primary key)Table 1: id1 - id2Table 2: id2 - name2 Table 3: id3 - name3Table 4: id1 - Id3What I want to do is have sql code that :Select data in id1 and id3 columns for which name2=input=name3 Insert into table 4Only insert into 4 if id1, id3 combination doesn't exist in table 4Currently I can do step 1 and 2, but (assuming it can be done) I cannot get the syntax for "NOT EXIST" correct for step 3.This is my code currently:INSERT INTO table4( id1, id3) SELECT id1, id3FROM table2INNER JOIN tab...Read more

sql - mysql update table.coloumn from other table.coloumn

I have MYSQL tabels like this#table_1 id questioner_name question_id field_3------ --------------- ----------- --------------- 10 name1 1 11 name2 2 12 name3 3 13 name3 3 #table_2 id questions answers------ --------- ------- 1 question1 answer1 2 question2 answer2 3 question3 answer3 4 question4 answer4Can I update table_1.field_3 from table_2.id l...Read more

mysql - how to export SQL query to excel with column names

The code below will accept an sql file and export the data to excel using xlsxwriter but I cannot seem to get it to export with the column names as well (header). I can get it to display the column names onto the screen though, but cannot get it to add it as the first row to the excel file.I did notice that if I change to using DictCursor instead of SSCursor, the excel file will instead be filled with just the column names repeated many times.import MySQLdbimport sysfrom xlsxwriter.workbook import Workbookimport MySQLdb.cursors as cursorsreload...Read more