I want to find maximum value of the has_sold column in an specific province.For example in all rows with province = Tehran which row has the most has_sold value? This is my table: I had tried this SELECT DISTINCT city, max(has_sold) FROM fp_stores_data_test GROUP BY city, but I’m not sure it’s rig…
How to create a MySQL 5.6 Rank with JOIN and multiple sorting criteria
I am stuck trying to return the rank of an SQL query. The final row should show the rank of the sorting that is presented correctly. I should be seeing Rank = 1,2,3 but I get this instead … I’ve tried numerous variations of the SQL statement but I cannot strike on the solution. Ideally, I would us…
Inserting data frames to teradata by using teradatasql package
I am using teradatasql package which native solution of Teradata as a connector between python and Teradata to load data from DB. However, I want to insert data frames I created in python back to DB. Is it possible to write data frames to the database by using teradatasql package? Thanks Answer SQLAlchemy pro…
Update duplicate data with same unique-identifier
I have the following table in my database: So I want to generate Unique-identifier and Update the UniqueID column as shown below: If values of UniqueID are the same, then they must have the same Uniqueidentifier. I have tried to write a CTE as below: But am not getting what am looking for. Very knew to the wo…
Calculating avg time of activity in the morning
I am creating a query to keep track of students’ activity at school on MS Acces 2010. And I have a table as follows: My current attempt on getting the duration But I am not sure how to write the #(= 3Hours / Total number of AM per person per day) part in SQL. Any help is appreciated. Answer Try
Finding the overall data from specific date using bigquery
I am creating a query using bigquery that will calculate the overall number of data within 5 days from a specific date for each ID. So far I managed to get the result where the result return the data for each day (day 1 until 5), which means there are 5 dates in the result. However, what I want is
SQL Query with Group , Order by and Random at the same time
I was unable to find anything similar to this problem. As we see John and David has more than one row and there’s a Order column so we can order it simply using ORDER BY Name ASC, Order ASC but this is not exactly what i need.. Here is the problem: i would like to know if its possible to
Presto equivalent of CONCAT_WS
I’m looking for a function in Presto to concat two columns with a separator like underline. Answer Your are looking here for the array_join function, see docs. array_join(x, delimiter, null_replacement) → varchar Concatenates the elements of the given array using the delimiter and an optional string to …
My SUM query returns 2 rows, need some advise
I am getting 2 rows(9500) for 111 in the result, could you please advise on the right approach, I need like, Balance = (sum of bought – sum of sold) Query Answer You can use conditional aggregation: If you want this for only one account, you can add a where clause:
How to resolve the invalid number in orcale
I am facing an issue with some data that start with a strange character before the number 5 how can I discover all of these characters and remove it what do you recommend to resolve this issue knowing that I get the data from a specific source so I can not change anything but I am trying to mask it