I’m referring to MS Access SQL. Suppose I have a column with dates of birth of a population and the decades which these dates fall into. I want to create a query which will show how many people were born in each decade of a century. I know it’s going to be something like My problem is that there might
Tag: sql
Translate SQL statement in R code using dplyr
I need help transalting an SQL statement regarding this dataset https://www.kaggle.com/datasets/hugomathien/soccer , into r code using dplyr. The SQL statement is : The r code that i have tried is: and i get this error : Error in dplyr::common_by(): ! by required, because the data sources have no common variables. Run rlang::last_error() to see where the error occurred. Run rlang::last_error()
Return distinct results that appear more than once
I have the following data: Result should be: Note that the result find unique combinations of ID and Site that repeat more than once for a given ID. I did the following query but does not return the result: Answer See: DBFIDDLE The SELECT ID, site FROM table1 GROUP BY ID, site will select the distinct values. Then, using HAVING
DATEPART and DATEFIRST alternative for redshift
I’m trying to run in Redshift with the start day being Saturday. I tried using different syntax for SET DATEFIRST but it seems like it’s not supported (or I just can’t find the correct syntax for redshift). Are there any alternatives available? The documentation page only shows information for extracting a specific dow (day of week): https://docs.aws.amazon.com/redshift/latest/dg/r_DATE_PART_function.html Answer The date_trunc()
How to give access to columns to new user when current user does not see the tables?
Using SYSTEM user I create a new user. After that I try to give this user access to specific columns only in 1 table, but get the error that this table does not exist. Meaning that SYSTEM user does not have access to this table. Unfortunately, the user that has access to the normal production tables cannot manage user privileges
query to get statistic data from SQL server
I have a table named total sales. In this table there are sales data like invoice date and branch name that sells the invoice and the quantity. I am trying to make a query to get total sales for each branch in every single date and my code is below, but when I execute the code the query returns an
Which metrics to compare when evaluating SQL query performance?
I recently watched an online course about oracle SQL performance tuning. In the video, the lecturer constantly compares the COST value from the Autotrace when comparing the performance of two queries. But I’ve also read from other forums and websites where it states that COST is a relative value specific to that query and should not be used for an
Search in the database
This Messages form display table with these informations (ID,FROM,TO,TITLE,MESSAGE). I am trying to search for all the messages send to a certain user . user will enter his name in the Search_textBox then it will filter the table to keep only messages to this user. I get this error : Answer this is Correct
How to create a constraint on custom datatype in SQL Server
I want to create a custom datatype in SQL Server with some sort of constraint that checks and validates the type by running through a custom function and returns an error if it fails. For example I have some sort of a Hebrew calendar in SQL Server, with a function that converts from a Hebrew date to a Gregorian date,
Convert a SQL query to the ElasticSearch query
I wrote this query in SQL and now I needed it in the elastic search. How can I do that? Answer Here you go: You need to use should clause for or and must clause for and. You need to use term or match query based on your requirement.