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 to do this apart from concatenating the datasets and
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 other (2020-08-20 and 2020-08-23), they would be counted as duplicates,
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 and results, it seems like you just want aggregation