Trying to return a 2 dimensional table from SQL query (Postgres). Read about Pivot Table (but not sure if it is valid for join) and crosstab/tablefunc but no luck. Expected result App A | …
Tag: count
check if value is available in DB or not
I have a table which has the schema: i need a query for the report which contains whether the name i give exists in the DB . the name AD,BC,CA should be given by me and its not in any other table. Thanks in advance Answer Use an outer join against a values list:
How to get observation frequency counts from multiple dataset into one table?
I have a bunch of large datasets. DS_1 (contains all unique IDs and names): DS_2: DS_3: I’m looking to create a new dataset that shows frequency counts across the datasets (and lastly calculates Total_Obs). I would output something like this: The datasets are fairly large. Is there a more efficient way …
Postgresql weird outcome after joining on dates with interval of 1 month
I have a table like this: The table indicates the date a user accessed a website and user_id is the user id. I wanted to check whether the user logged in a month and in the previous month. I wrote the next query: However, when I run this query table a shows the previous month and table b is the
How to count SQL based on a DateTime and an other variable?
I have a SQL table with failed testresults: This table is telling me that the test has failed, but I want to know if it is the first, second or third test. In the end I would like to do it with a logo where a x stands for a failed test, and a X for the concerned test, like
How to find duplicate records in MySQL, but with a degree of variance?
Assume I have the following table structure and data: I’d like to be able to find duplicate transactions where the description and amounts match, but the date would have some degree of variance +/- 3 days from each other. Because the “Burger King” transactions are within three days of each o…
Counting values within the same row in SQL Server
My SQL Server problem is as follows: let’s say we have clients and we wish to rate them based on 4 categories (Score_1…Score_4) on a scale of 0 to 2. I have a table presented below: What I want my code to do is count the number of 0, 1, and 2 values each of my clients recieved. The result
How to find whether the same customers who ordered this month also ordered the next month?
I have an orders table Now I want to find how many of the users ordered in 1st month also ordered in the next month. In this case, user_id’s 32,24,27 ordered in 7th month but only 24 and 32 ordered in the next month. I want the result to be like : I’m lost here. Can someone please help me
Convert Postgre query to Hive/ Mysql
I have this table: I want to a situation where each footballer appears only once in a new table. For instance, Messi appears twice, but I want to take any occurrence of Messi in the new table. I am not sure how to convert it to either Hive or mysql. This is what I want the desired results to look
Calculating a 7 day average
I have this query. I want to generate the 7 day average of the mappings column. For example, I want to calculate the average mappings for each day (from 2020-01-03 – 2020-01-10). Results: Then return avg(avg_mapping) Answer I don’t see how your question relates to a rolling average. From your data…