I have a psql table I want to get a break down of users points that shows their points the current month, current year, and the lifetime. I’m a bit lost on how to do this. What would the query look like? The final table should look like this Answer How about conditional aggregation?
Category: Questions
Conversion failed when converting the varchar value ‘U’ to data type int
I’m getting an “error converting varchar to int” for fields Q1_c and Q2_c. Those fields are CHAR(1) and the values for those fields are either 1,2,3,4,5, or U, but it will not convert when there is a U, so I am trying to change the value of U to something else like 0. I have also tried CAST(…
Append time from table to getdate() while using case
I am trying to append time from a column to a date and use in a case statment getdate() at time zone ‘Central Standard Time’ this is what i’ve tried so far datatype of endtime column is nvarchar and the format of the time is 21:00 and this is the error that i am getting Incorrect syntax near…
How to get conditional SUM?
I am trying to get a conditional sum based on another column. For example, suppose I have this dataset: If there is a type of 1 then I only want that data for that data, else if there is only 0 then I want that data for that date. So for 12/16/2019 I would want the value 0. For 12/23/2019
getting first sunday using the window function
I need to get the first Sunday from each month and add that as a new column to the original table. The below query works only when the first row of a group starts with Sunday i.e for March month. It doesn’t work when the month is not starting with Sunday. Query: Incorrect result How can this be solved u…
How can I use the MySQL COUNT() statement on a generated query?
I’ve created a query that finds the users of a specific site that have liked every photo on the site and stand out as potential bots. I’m trying to count the number of bots. My query currently …
Is it possible to process external data as some kind of virtual table?
I have the following problem I need to make a funnel, where I need to show correlation between the data I have and data from DB. I have a query of the following kind: Is it possible to process the data in where statement in such a way that I could address to it as to a table I mean
What is the equivalent of a collection and a document of firebase in sql?
My question is that what is the equivalent of a collection and a document of firebase in other databases like SQL or MongoDB, I know that there is a table in SQL but what is the equivalent of the …
Deducting values from 2 case statements from one another
I have two CASE statements and I’m trying to deduct the value of one from another, but I can’t get the code to work. Does anyone have any suggestions where I’m going wrong? The 2 CASE statements are: and When I try to combine these I get errors (missing right parenthesis). The combined code …
What are the categories for which average revenue is above the overall average
How do I pass the greater than condition? I’m getting an error as invalid column name ‘average’. Here is my code: SELECT P.prod_cat, AVG(total_amt) AS average FROM Transactions T JOIN …