My database is something like this: I only need the counts of the dates like this: I’ve tried … … but the date format is not my friend. Any ideas would be appreciated. Answer In most databases, you can remove the date component by converting to a date and using group by: However, various dat…
Tag: count
insert same record multiple times based on the given count
I want to insert ABC thrice, EDC four times, FDC twice in a table using single SQL, is it possible? The output of the query should insert into the following table with the following entries. Thanks Answer You would typically use a recursive query: Here is a demo; the syntax works in both Postgres and MySQL 8.…
Oracle SQL: select from a set of orders only those that contain certain types
I want to select from a set of orders only those that are marked with certain types. Example: Let’s assume I have a table X. In my case I want to select only those orders that contain both A and B: My work so far: However, I don’t like this solution. Is there a better approach? Answer Use an analy…
output need to achieve by one query
I have table which consist data like below. I need output in pivot : I can achieve by separate queries easily but is it possible to get it done by one query? Answer You can use conditional aggregation such as in order to return results pivoted.
Select from multiple tables with group and join
i have 4 tabels drinks, opskrifter, ingredients and stock the tables consist of drinks drink_id name beskriv glas image alcohol opskrifter drink_id ingredient_id quantity ingredients ingredient_id name stock ingredient_id name i want a query to select drinks that can be made in opskrifter of the ingredients i…
Return only recipes that contains all ingredients Sql
I have these 3 tables: Drinks drink_id name Ingredients ingredient_id name Opskrifter drink_id ingredient_id quantity Drinks and Ingredients are cross-referenced in opskrifter. I want to return all recipes from opskrifter that have ingredients from another table called Stock name So to make a gin and tonic, I…
Pgsql find similar records in database
I have a table that has the layout of this sensor_id time value 1 2020-12-22 09:00:00 20.5 1 2020-12-22 10:00:00 21.5 1 2020-12-22 11:00:00 22.5 1 2020-12-22 12:00:00 23.5 2 2020-12-22 09:00:00 …
SQL SELECT showing records only if the number of records is greater than N
I have a table defined like this (MySQL 5.1): Sample dataset: I need to show records matching a certain criteria (lets suppose lastname = ‘Santucci’ ) only if the number of records is greater than a certain defined limit (lets say 2). I tried in various way without success the most promising form …
SQL query for report by name with many columns count by status
Is it possible by sql query to produce a report group by a name with count by status. Tables: Project (id, name, status_id, service_id) Status (id, name) Service (id, name) I need to make a report, count by status group by service: Columns results: Service name, status name 1 , status name 2, status name 3, t…
Using Over Partition in sql compared with group by
Given the table creation code bellow, is there an alternative method(s) to display the same result to using group by and count(*)? And what’s the difference in this case? Answer This query puts the total for each colour on each row: Before window functions, a typical solution would be a correlated subqu…