I am trying to achieve the following… I get the following error: Why can’t I reference the relations table? Answer Based on your CTE, this is just a guess, but I think you probably want: We don’t know your table structures, so I can’t say I have the right columns for that join, but I&#…
Tag: mysql
How to solve “SELECT list is not in GROUP BY clause and contains nonaggregated column” without adding the column into “group by”?
table: id action project_id created_on last_updated_on 1 Update 123 2021.1.1 2021.5.3 2 creation 123 2021.1.4 2021.5.2 3 Update 123 2021.1.3 2021.5.1 4 Update 456 2021.2.1 2021.6.3 5 Update 456 2021.2.2 2021.6.2 6 creation 456 2021.2.3 2021.6.1 I would like to get a map of project and its most recently create…
SQL query – split out count() results into one line of GROUP BY results
I want to use GROUP BY to get a row per month-year. Additionally, I have another field which, for this example, is colour and can have values of Blue or Red. I want to see a table with columns for Blue, Red, month and year = I want to count the number of each colour per month-year. Can I do
How to SUM values of a column and then order by another column using SQL?
When I SELECT * FROM table; I have the following result: So I try to sum the total of votes with SUM(votes) as tt_votes and then group the result by candidate to have a desc order. state column shows the first (or the last?) record for the candidate, but what I’d really like to have is not only group by
mysql: Select Max value and delete other rows
I need to get the row with the maximum status value for each employee_id and keep only one record for employee_id If status is equal for the same employee id then pick one and delete the other, Thank you and appreciated Table: ID Type Status emplyee_ID 1111 A 0 10 2222 A 1 10 3333 B 0 20 4444 B
List which has no reports submitted
I have two tables in MySQL database. tbl_sites (similarly around 3500 sites) tbl_reports (Similarly multiple reports from each sites) I want to get the list of sites by site name, where the report count is still zero. A report is considered as successfully submitted when the report_status = 2. Answer Using ex…
How to SUM all subchildren tree in SQL recursively?
Good day, I’ve been pulling my hair on this problem for a while ><” I have 4 categories in a tree structure. tenant_category_transaction_view: I would like to have the sum of all children “sumSubtotal” on every category Something like that: I’ve managed to come very close&#…
SQL UPDATE base on COUNT
I have two tables product-table with fields (id, category_id) category-table with fields (id, num_of_products) The product table contains multiple products in different categories. And I know how to count product in each categories using But how do I update category.num_of_product using a single sql query wit…
How to query the percent of totals in a group by query
I have a query that pulls the total number of records grouped by the year and length of characters in the identification number. I want to add a column that calculates the percentage of this total against the total for each record year. This is the query that I am using, and so far I’m able to pull the …
Interpolate Multiseries Data In SQL
I have a system that stores the data only when they are changed. So, the dataset looks like below. data_type_id data_value inserted_at 2 240 2022-01-19 17:20:52 1 30 2022-01-19 17:20:47 2 239 2022-01-19 17:20:42 1 29 2022-01-19 17:20:42 My data frequency is every 5 seconds. So, whether there’s any times…