Below is an example of the table data I have I would like to generate a result set as below I tried to use window function OVER() and PARTITION() but I managed to get average on month by month rather than starting from the year. Any suggestions, please. Thanks Answer I think you want: This puts each year in a
Tag: select
Combining MySQL Queries to get total amount
I’m trying to combine the following 2 queries. table_A stores a list of available appointments and number_open is how many open slots there are for a specific appointment, in this case 2020-06-30 AM which has 5 open. table_B contains any dates waiting to be accepted and has a different record for each date request, so in this case, 2 rows
Grouping together results of multiple GROUP_CONCAT() with distinct values only
second attempt at this question with more detail. I am attempting to group the distinct values of multiple columns together for objects of the same name. I can use GROUP_CONCAT on each of the ‘Type’ columns, but I can’t merge them together to get a distinct set of values for each name Here is a sample of my data: And
Return the highest count record
The data I am working on looks like below- A_ID B_ID count 123 abcd 1000 123 aaaa 2000 123 aaaa 3000 456 null …
SQL query on two tables using two column as keys
I started studying SQL (not MySQL as it seems to be different in some parts) and I encountered this problem that i could not figure out how to solve I have the following tables: CREATE TABLE …
CTE Recursive Query to Grandparents
I have following table: What I need to get is SELECT query that will return only rows that have ISDELETED 0 and ISINEDIT 0 and whose parents or grandparents are 0 as well I currently have: But for some reason it returns double rows Answer You need to add the same isdeleted = 0 AND isinedit = 0 predicate to
MySQL Debit/Credit cumulative balance
I would like a cumulative DR or CR balance for the following table: For the above table, a cumulative Balance column would be: Decreasing a DR below 0 makes it a CR Decreasing a CR below 0 makes it a DR Where balance is 0, value is 0 DR It could be calculated as CR’s being a negative value, but
MySQL-Statement. How do i skip rows in query where all rows equal null
I have created a query to construct a table with the following code: SELECT Date, SUM(CASE WHEN UniqueId = ‘NonComLong’ THEN Value ELSE NULL END) AS ‘NonComLong’, SUM(CASE WHEN UniqueId = ‘…
Query to find courses that all members are enrolled in
I have a database which contains 5 tables. And I want to find the names of courses that are enrolled by all “Automotive Engineering” students. I tried the below statements but it shows nothing. I execute the above query but it shows nothing. Can someone help me from here? Answer This is a kind of relational division problem. Here is
How can i find rows before a specific value?
I have the next row and what I want to do is to select all the rows before the type “shop”. I tried using case in the “where clause” but I didn’t get any result. How can I do it? |id|visitnumber|type …