I have a network created MYSQL table with following fields: IP_SRC, IP_DST, BYTES_IN, BYTES_OUT, START_TIME, STOP_TIME 1.1.1.1 8.8.8.8 1080 540 1580684018 1580684100 8.8.4.4 1.1.1.1 2000 …
List all teams and the average raw scores for all members
My query is giving all the teams the exact same average player score. When each team would should have their own average score based on their team members. select Teams.TeamName, avg(Bowler_Scores….
How to retrieve a unique data output using sql query (DISTINCT)?
The data set is like this: Table A ==type===|| ===score==== Two columns type ands score, and Table B ==== id ==== || ==== data ==== Two columns as well id and data. So, the details are as …
Create Redshift table with new Geometry type through psycopg2
After Redshift announced support for Geometry types and spatial functions, I’d like to create a table with polygons for all countries. I’m failing to do the INSERT and would appreciate help. Here is …
SQL inner join and where performance comparison
If we have 2 tables, tableA (with column1, column2) and tableB (with column1, column2), what’s the difference between the following two queries? Which one has better performance? What if we have indexing for both tables? Query #1: Query #2: Answer Both will perform equally. One is an ansi style and the …
Replace conditional subquery in Order By (Performance consideration)
I have a ratings table (t2) I’m using to sort queries by lower_bound DESC. The problem is, the default value of lower_bound has to be null or 0. When a rating occurs, the row is updated with Wilson’s rating algo. This means that new entries get ordered before all other entries in a lower_bound DES…
MySQL: How to compare multiple field value of a row and get matched or lowest one?
I have a MySQL table (below): I can fetch the min or max of the column values simply but my problem is: If any 3 or more fields value matched then fetch that value (if 3 fields values matched more than one value then the lowest of those values), for row #1 2100 will be the output and for row#3
Sql: Find sum of column from second table using date from first table
I’ve been struggling to build a query that calculate the sum of column called ‘TIDAL_VOLUME’ with respect to date value that’s coming from another table. Please see the content of the Table_1: Please see the content of the Table_2: Note: TIDAL_VOLUME might have NULL as well. Now, the s…
Calculate interval between boolean column change
I have a table with measurements of weather here is a simplified version of it: and I need to calculate how much time is been sunny. I know I need to use window functions but I’m stuck. Because I need this interval to be calculated in some range for example for last day. I have been able to make a
Add amount from one row to amount in a different row with same primary key
I have a query where I would like to add the amount from one row to the amount of another row. Basically for any specific LLW, wherever WT_CPDS = ‘ASBESTOS’ and the first digit of FYP_NUM is not 5 or …