I am trying to find the drinker and total amount of money spent on drinks for all drinkers in February 2020. I also need to include drinkers who haven’t ordered a drink in this period. Here are the three tables: Here is my statement so far: I am very new to SQL, I know that there are a number of
Tag: mysql
MySQL: Counting active products that belong to active categories (and optionally to active subcategories)
i’m using MySQL 5.6. I need to get a list of categories, with a product count column. So far, a very easy and trivial task. Here’s where it gets a little complicated: A product has a category, and can also have a subcategory (optionally). Categories, subcategories and products can be active (flg_p…
MySQL (Laravel Eloquent) – Get record when two columns have two or more values at the same time
I have this database that I got from this post that manages products and its variants: The problem is, that I don’t know how would I get the SKU at the moment that a user selects the options of the product he wants: Let’s suppose that the user selects the product with ID 1 and the options size-sma…
SQL Combine two queries and SUM
Ok I am a little rusty in SQL. I have 2 exactly same tables with columns ID, Name, Gender, BG. BG is a internal reference. I can query each table individually: SELECT Gender,BG, COUNT(BG) CountValue …
How to calculate percentage in group by with condition?
I have a table with this schema: I need to know percentage of failure for example: google -> 50% failure Facebook -> 100% failure I use mysql 5.7 I tried to reach this destination using group by, but I couldn’t Answer You can do conditional aggregation. I like to do this with avg(): For each provi…
MySQL conditional querying many to many relations (inner joined tables) from an array
I’ve a colors table and an items table, with a many to many relation between these 2 tables (via an items_colors table). An item can have many colors and a color can have many items. From an array of colors, I would like to get all items that only match one or more provided colors. If an item is also
How to calculate AVG, MIN & MAX of date interval of a column in a table?
I have a table more like this +—-+————-+———————+ | id | order_id | transaction_date | +—-+————-+———————+ | 1 | TEST000001 | 2018-01-01 00:00:10 | | 2 | TEST000002 | 2…
Full Text Search on MySQL : Can not search by word after hyphen/dash
I have the following record in DB: ‘Vinyl Exam – CA Only’ So, when I performed the following search: Record is returned, but in this case: Item is not returned. What I should do to return the item in the last case. Answer The reason your search isn’t working is because by default, ther…
Unexpected results from query pulling oldest 50 songs in database with unique artists
I’ve got a music player system which is supposed to play each song in my database, mostly randomly, before repeating any songs. My problem is, there are some very old songs in the database that are being skipped. My query is set up to pull the 50 oldest songs in the database but with no duplicate artist…
What are the disadvantages when I use mutually dependent foreign keys?
I know that it’s not a good method to use two tables with mutual foreign key definitions, but I don’t understand the difficulties that arise in the database. An example: I’ve a table city and a table state. City.city points at state.capitalcity and state.state points at city.state. I would a…