I have a table as follow. In python with datetime.fromtimestamp we can change the integer time to date. I want to change the time column to MM/DD/YY in Mysql. Can you help me with that? Answer I forgot until the comment from nbk above that FROM_UNIXTIME() does support an optional second argument, so you can d…
Tag: mysql
MySQL: Find the last event where the user was tracked
Let’s say your web application tracks the activities of its users using a tracking system. While a user hasn’t logged in or signed up, all the user’s actions are tracked using anonymous_id and the user_id is null, and afterward, they are tracked using the same anonymous_id and user_id. It is…
Check if a pair of records belong to multiple group IDs
I have a table that contains 2 IDs – UserID and GroupID. I need to pull a list of all UserIDs that “share” the same GroupID at least 4 times So, based on the following data set: I’m trying to generate the following result: UserID A UserID B NumberOfOccurrences 1 2 4 2 3 4 1 3 5 I’…
how to single out certain names in sql
So I have this problem in SQL where the question is this: List the sId and name of students that applied to “WSU” But not “U of O”. and my attempt was this: However this does not do the trick as it doesn’t catch that some of the students had already applied to U of O. MRE: And then of
Joining Query with codeigniter Model
Please help me how can I get only response where activities.activity_id = response.activity_id? here is my CI_model My Activities table My Response Table My users table Answer Try with this: You need to add aliases to the tables in order to build a more simplified and ordered query, in this case the aliases a…
SQL Two joins; First one a left join; second only join when combination does not exist yet
I have the following problem. I have the following 3 tables, I can’t edit those. I have a “main” table I would like to join “Table1” to, I succeeded (see fiddle). The next step is to join “Table2”, but only when the Charge-Name combination does not exist yet. If it doesn’t exists…
SQL help i need to find the inventory remaining in my office (i want to know unallocated asset remaining ))
In sql help i have 2 tables, table one is asset table which is as follow id asset_code asset_name asset_group asset_quantity 1 A001 demo asset 4 5 2 A002 demo asset 2 6 3 and another table is asset_allocation id asset_id allocated_quantity allocated_location 1 1 2 IT office 2 1 1 main hall now the scenario is…
Query assigning a null to a column if a dependent column is also null in sql
I have a table that contains dozens of athletes and the hours they spent at the gym everyday. My goal is to calculate their total hours in a month. They were supposed to go every day, and if they missed any day (null for that day), I want the entire total hours column for that athlete to display null, not
How to Count the number of Countries From registered users in my users table
I have two tables country and users. The country holds id’s for every country in the world. When a user registers into the system the id of country is saved in a column named country as a foreign key. Now what I want to do is to count how many countries users registered, for instance 3 users registered …
Get non-aggregated column values without joins MySQL
I’m using mysql 8.0 and the table I have has a lot of rows so the solutions from this link take too long to run. Table example: ID Name Value Category 1 a 5 alpha 2 b 7 beta 3 c 8 alpha 4 d 10 beta I would like to group it by category and then select the max