i have this query for calculate success total in each district. this query works but its take until 2min to output data, i have 15k rows in orders. the output should be like this can you help me to improve the peformance? Thanks Answer Try performing the grouping/calculation in a joined “derived table&#…
Tag: mysql
How to list data from join table in one row?
There 3 entities: 1) Question, 2) Tag and join table between them – question_has_tag. When I make a select query like: I’m getting the following result: and I needed to get: How to get it ? Answer You need to GROUP them and use GROUP_CONCAT Like
What is difference between `flag is TRUE` vs `flag = TRUE` in MySQL?
I ran the following queries in MySQL – Time taken = 1 second. vs Time taken = 66 ms. I have indexes on (valid, priority) and (valid). Why is there such a huge difference? What is the difference between Is TRUE vs = TRUE ? Answer As per the Mysql Doc for IS operator IS boolean_value Tests a value against
SQL get Values where some is greater than value with Having not working
I have this code: But it gives me an error. I can not figure it out. Happy for every help! Answer Having clause execute after group by and then select so you have to use aggregated function with having as following
Mariadb 10.4 Data truncated for column ‘column_name’ on insert but not on update
I have the following table (mariadb 10.4) called p: I currently have a couple of million rows on this table with the sources a, b, c, and d. Just recently we applied a migration to add source_e and we started getting the error ERROR 1265 (01000): Data truncated for column ‘source’ at row 1 when tr…
MySQL Running Total By Group, Time Interval
I would like to take a table of customer orders like this: And create a table calculating a cumulative running total by week, segmenting the weeks by 7 days starting at the earliest date (2020-03-01, 2020-03-08, etc.). Something like: Thanks for the help! Answer You can use aggregation and window functions (t…
Subtract scanner value from sql table value
I’m trying to subtract a scanner input from a table value. Here’s a screenshot of my table: Here’s what my code looks like: The output of my code is: Here’s what I want my table to look like after subtracting 100 (my scanner input quantity): I’m unsure as to why subtracting my sc…
MySQL Order By Date And Boolean Priority: Number To Date Weirdness
I have a table which I want to get sorted by date_created descending, but there is also a pinned column which if that is 1 the row should be at the top regardless of date. The query I’ve come up with, …
Left outer join is working with SQLite while is not with MySQL
I have a table with products, orders, prices, date of sell. I need to create a table with the sells for a product in a single day. I used sqlite and this code worked just fine: /* Groups order date &…
how do i name a similar column from sql with different column names in a select statement
There is a column ‘client_code’ in sql table ‘tb_accounts’. I want to create a select statement with two column aliases ‘cl_code’ and ‘acc_num’, but all populated from the ‘client_code’ column. Answer Select results are aliased like: