please help me. imagine the table as bellow and I want to query and show result like this I dont know how to write query to show result like that. I used (date_join) as dates, count(id) and group by date(date_join) and the result not show like that. Answer In MySQL 8.0, you can solve this with aggregation and…
Tag: group-by
How do I assign methods to a specific parameter using PIVOT in SQL?
We’re working on producing our data as a relational database/set and as a row-column dataset (so that the users of the data can use which format suits them best). Here’s a simplified table of our data:…
Improve query performance and maintainability
I have a query like this: The output of the query will be something like this, basically a CODE and the respective COUNT, for example: After this first query, I have a foreach that will iterate the result of the query above. Inside the foreach, for each result of the query above, I want to get some informatio…
get first row fo each group SQL
I have data like below. I would like to get the top choice for each gender from the following data I have tried mapping the choices by descending order as below : BUT, I would like to get the following table where i get the top choice for eachgender : Answer If you are runing MySQL 8.0, you can use
Query for Exam Score calculation
Problem Statement: I have an EXAM table which has following fields and sample data I want to write a SQL query to output the following fields My SQL Query: Can I achieve my result by writing a better SQL query? Answer You could indeed join the table with several aggregate subqueries that compute the relevant …
MySQL Group By for sorted table
I’ve two tables with these structures: Table 1 (comments) Table 2 (posts) My goal is to get a list with the latest comment from each post of a specific category_id. So for example, if I have category 1 as input I need the comments with the id 2 and 1 because these are always the last comments for each p…
Delete registers with rownumber greater than specified for each group got from sql
I have a table of people with their commments on a blog . I need to leave the last 10 comments for each person in the table and delete the older ones. Lets say the columns are: personId commentId dateFromComment I know how to do it with several queries but not with just one query(any subqueries allowed) and f…
Get the average of values in every specific epoch ranges in unix timestamp which returns -1 in specific condition in MySQL
I have a MySQL table which has some records as follows: I want to compute the average for every 10 epochs to see the following results: I saw some similar answers like enter link description here and enter link description here and enter link description here but these answers are not a solution for my specif…
Get aggregated average values joining three tables and display them next to each value in first table
I have three tables which you can also find in the SQL fiddle: CREATE TABLE Sales ( Product_ID VARCHAR(255), Sales_Value VARCHAR(255), Sales_Quantity VARCHAR(255) ); INSERT INTO Sales (…
SQL sum grouped by field with all rows
I have this table: I want this result: I tried this: But get the error that subquery returns different number of rows. How can I do it? I want all the rows of sale_lines table selecting all fields and adding the sum(price) grouped by sale_id. Answer You can use window function : If you want sub-query then you…