This should be easy, but I’m having trouble with it. I have a results table that looks like this: Notice that the pct column sums to 1.0 for each store. Here’s the code to create this table: I’m trying to group the results by store, then by cust_id while creating a new category A&B if th…
Tag: mysql
fetch id who have register all adv
There are 3 types of adventures for which I used distinct function in query. There is only one 1 customer who have booked all types of adventures. The query i used to fetch the data is: Answer You can get the customer ids using aggregation and having: Or, if you don’t want to hardcode the “3”…
Best way to ignore null values in MYSQL
My data looks like this, I have multiple values in the Table field with each having its own unique values for the Data field. However, Data values for TableA will be the same format in the table …
Count distinct value of column regardless change in other columns
I have the following table: And I want to count distinct Whispers knowing that there is a change in client column, currently I’m using this: But I see that W1 and W2 are counted twice for each client but I need to consider them as one no matter the client is, so for the first time If I got iOS
Check datatype of column after applying function MySQL
I want to know datatype of column after using a function. After using timediff function what datatype is it? I’ve looked at the doc to search for output datatype of a function https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html but could not find, Also it would be inefficient to memoriz…
Group by sub-string while selecting the full string
Let’s say my sql dataset looks like this and it has one column: John John,2345 Joe,1234 Joe Joe,1234 John John,2345 John,2345 Smith Smith I am going to count the number of times …
How to write multiple queries in a single tables.sql file and upload it using PHP?
All I want is to create 2 or more tables using tables.sql file via PHP. PHP create_db.php DB_HOST, DB_USER, DB_PASSWORD, DB_NAME are defined in config.php file PHP v7.4.7 SQL tables.sql If I upload this tables.sql file directly to MySQL server using command line it is working. MySQL v8.0.20 MySQL Comunity Ser…
Mysql group by get value base of another colum value
I have one long query return like below table Could I use group by to achieve below result Basically, i would like to to group name , and the value is based on priority without join the query itself Answer You can use row_number()
Find the names of last queried tables
I have a query inside my PHP program like I want to know the names of the tables called in this query, including joined tables. The aim is to find the schema of the affected tables which is required for further operations. Is there a direct query to do this or will I have to use some Regex? Answer You
How to join two unrelated table in MySQL?
I have two unrelated tables and I need to join in a single row as expected output below. I tried below query and doesn’t work. How to make join these tables?? Table 1 | col1 | amount| | a | 200 | …