Transpose rows into columns in SQL Server, I try it using pivot but, expected result is not reached, using pivot getting Max value only Expected output: Answer You can use conditional aggregation with row_number():
Tag: aggregate-functions
Aggregate data based on unix time stamp crate database
I’m very new to SQL and time series database. I’m using crate database ( it think which is used PostgreSQL).i want to aggregate the data by hour,day ,week and month. Unix time stamp is used to store the data. following is my sample database. i tried the sql query using FROM_UNIXTIME not supported . please help me? im looking the
Postgres SUM over given time
I am trying to SUM google analytics data over a given time using Postgres. I am not quite sure how to get around a grouping issue. The query you see below returns one column per day. I want to SUM all …
SQL: Expression Not in GROUP BY Key
I have a transaction table t1 in Hive that looks like this: store_id cust_id zip_code transaction_count spend 1000 100 123 3 50 2000 200 …
Find Nth value and average value corresponding to each group in SQL
I have a table with name and grades for some users CREATE TABLE grades (name varchar(100), grade integer); insert into grades values (‘Bob’, 12), (‘Bob’, 13), (‘Bob’, 23), (‘Bob’, 17), (‘James’, 15), (…
Making a pivot table group by users
I want to see user statics, so I made query: It’s working. Here is the result: But I want to make a pivot by Months (last 6 months)… Table: obj_odb_l l ->date column -> l.datum_p -> trunc(l.datum_p,’MONTH’) How can I make a pivot table ? Answer Consider adding the month expression, TRUNC(l.datum_p,’MONTH’), into above aggregate query. Then run the query
Subtract values from one column based on values in another (TSQL)
I have table where I need to subtract values in one column based on conditions applied to the other. For example, I want to subtract values with code fst and trd, meaning (12 – 23). I don’t want to declare separate variables. Is it possible to make this with a query? Answer One method is conditional aggregation: Assuming you have
db2: how to remove the decimal from number column?
A very simple query which work the result is I want to remove the ,00000000000 to obtain I have tried cast and round but without success. I’m on Db2 This nice ltrim works fine for all type of cars but using with avg return the string with the 00000 the result is.. 🙁 Only the , is changed. Answer casting
If all result equals, return true
I have a simple select that returns a single column: result: {1,1,2} I want to check if all result are 1 then return true and if one column have a 2 return false. Tried something like these: But obviously it returns true if a value is 1 and false if it is 2, instead of just one result. For example:
SQL- Invalid query because of aggregate function with simple calculations for each date and ID
I very new to SQL (less than 100Hrs). Problem case is as mentioned below. Every time I try a query either get incorrect output or error that “not contained in either an aggregate function or the GROUP BY clause” Have tried searching similar questions or example but no results. I am lost now. Please help I have three tables Table