FAQ Database Discussion Community


Update aggregate columns inside stored procedure

sql-server,tsql,stored-procedures,aggregate-functions
Consider a stored procedure that updates aggregate data for a specified phone_id on a specific day call_date. create proc phone_calls_aggregate @phone_id int, @call_date date as begin -- check if aggregate table already exists if not exists (select * from sys.sysobjects where name = 'tbl_calls_agg') create table tbl_calls_agg ( -- create...

Subtract two columns of different tables

sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

Get last element of an ordered set in postgresql

sql,postgresql,aggregate-functions,greatest-n-per-group
I am trying to get the last element of an ordered set, stored in a database table. The ordering is defined by one of the columns in the table. Also the table contains multiple sets, so I want the last one for each of the sets. As an example consider...

Creating an aggregate function fails

aggregate-functions,postgresql-9.1
While looking for an aggregate function that returns the nth element of a collection I found the following solution: CREATE OR REPLACE FUNCTION _final_nth( anyarray, integer ) RETURNS anyelement AS $BODY$ SELECT a FROM unnest( $1 ) a ORDER BY a offset $2 LIMIT 1; $BODY$ LANGUAGE 'sql' IMMUTABLE; CREATE...

pandas groupby with two key

python,pandas,group-by,aggregate-functions
I took a whole afternoon trying to implement this task but failed ,I've got a pandas data frame like this columns=[ka,kb_1,kb_2,timeofEvent,timeInterval] 0:'3M' '2345' '2345' '2014-10-5',3000 1:'3M' '2958' '2152' '2015-3-22',5000 2:'GE' '2183' '2183' '2012-12-31',515 3:'3M' '2958' '2958' '2015-3-10',395 4:'GE' '2183' '2285' '2015-4-19',1925 5:'GE' '2598' '2598' '2015-3-17',1915 What is to be implemented...

Invalid use of group function in mysql

mysql,aggregate-functions
I've a table like this: +----+------+--------+ | id | name | salary | +----+------+--------+ | 1 | Ajay | 20000 | | 2 | Aja | 2000 | | 3 | Aj | 200 | | 4 | A | 3000 | | 5 | q | 30000 | +----+------+--------+...

select statement for averages based on different date ranges in one MySQL query

php,mysql,datetime,mysqli,aggregate-functions
Basically I am attempting to make a chart with this data. I am able to put my query into a while loop in PHP to get each average, but I would prefer this was done with one query producing one result table. <?php date_default_timezone_set('America/Los_Angeles'); include('../connect.php'); $subcategory = 'T-Shirts'; $date =...

Finding average ratings of a product that customers have not bought

mysql,sql,aggregate-functions
I am trying to find for each customer the products that they ahve not bought, and which of these products has the highest rating. For example, in the tables below, John has bought items 1 and 2, but has not bought 3, 4, or 5. Products 3 and 5 have...

dplyr: filter rows according to aggregated function result

r,filter,aggregate-functions,dplyr
I have a table listing (amount, year and month) and I want to filter the rows corresponding to complete years. I.e. I want to ommit the last 4 rows of the sample dataframe I give below, that refer to 2015, and get the rest 60. Is it possible to do...

Query to count items across multiple columns

sql,postgresql,aggregate-functions
I have a table: ID | fish1 | fish2 | fish3 | 1 | shark | dolfy | whale | 2 | tuna | shark | dolfy | 3 | dolfy | shark | tuna | 4 | dolfy | tuna | shark | and the result of he query...

SELECT columns different from GROUP BY columns

mysql,oracle,group-by,sql-order-by,aggregate-functions
Having this database schema (just for illustration purpose) [articles (id_article, title)] [articles_tags (id_tag, id_article)] [tags (id_tag, name)] using MySQL it's possible to do: SELECT a.title, COUNT(at.id_tag) tag_count FROM articles a JOIN articles_tags at ON a.id_article = at.id_article JOIN tags t ON t.id_tag = at.id_tag GROUP BY a.id_article ORDER BY tag_count...

When using an aggregate function in SQL, will the associated columns be selected?

sql,group,aggregate-functions
I have the following query SELECT day, category_id, name, qval MIN(CONCAT(category_id, qval)) FROM facts WHERE day = CURDATE() GROUP BY category_id Will the name that gets selected always be the name associated with the MIN record that is identified?...

Not getting the correct SUM values in SQL Server 2012 when using a PIVOT

sql-server,sql-server-2012,pivot,distinct,aggregate-functions
I am trying to create a query that will pivot over some rows but will SUM some columns and then group them together. I've used the PIVOT function before but I am running into issues when my result set contains similar values. This is SQL Server 2012. Sample code: CREATE...

Find duplicated values on array column

sql,arrays,postgresql,aggregate-functions,set-returning-functions
I have a table with a array column like this: my_table id array -- ----------- 1 {1, 3, 4, 5} 2 {19,2, 4, 9} 3 {23,46, 87, 6} 4 {199,24, 93, 6} And i want as result what and where is the repeated values, like this: value_repeated is_repeated_on -------------- -----------...

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Subquery aggregate function with SUM(CASE SUBQUERY)

sql,sql-server,sum,subquery,aggregate-functions
I'm getting an error while executing my query Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Code: SELECT S.id, SUM(CASE WHEN sc.coverage IN (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)) THEN 1 ELSE 0 END) as sm FROM Storefronts s LEFT JOIN StorefrontCoverages sc ON s.id =...

How to get number of invoices for last 12 weeks in Postgres

sql,postgresql,asp.net-mvc-3,aggregate-functions,generate-series
Invoice database contains invoice dates: create table dok ( dokumnr serial primary key, invoicedate date not null ); Dashboard requires comma separated list containing number of invoices for last 12 weeks, e.q 4,8,0,6,7,6,0,6,0,4,5,6 List contains always 12 elements. If there are no invoices for some 7 day interval, 0 should...

MySQL Include 0 in count many to many relationship

mysql,aggregate-functions
I've got a table of areas that have an ID, a table of noise levels, and a table that records entries that have both an area ID and a noise level. I'm hoping to achieve something that will return a count of all of the areas's noise levels, even if...

SQL COUNT- not a single group group function error

sql,oracle,count,group-by,aggregate-functions
I run the following COUNT(*) query on Oracle DB: select count(*) + (select count(*) from t_diagram) from t_object I get the following error: Not a single group- group function. I understand that using aggregation methods (e.g. SUM, AVG) require a GROUP BY statement. However, how can I add a GROUP...

How to Calculate Aggregated Product Function in SQL Server

sql,sql-server,sum,aggregate-functions,exp
I have a table with 2 column: No. Name Serial 1 Tom 1 2 Bob 5 3 Don 3 4 Jim 6 I want to add a column whose a content is multiply Serial column like this: No. Name Serial Multiply 1 Tom 2 2 2 Bob 5 10 3...

How to convert miliseconds to date in mongodb aggregation?

mongodb,date,aggregate-functions,mongodb-query
I have a collection in MongoDB and documents like this - [ { "campaignId": 1, "operatorId": 1, "txnType": "DR", "amount": 2, "balance": 0, "txnTime": 1428907779206, "txnSrc": "Dial_In", "msisdn": "9789877667", "circle": "Delhi", "smsContent": "Hello Mr Arif" }, { "campaignId": 1, "operatorId": 1, "txnType": "DR", "circle": "Delhi", "amount": 2, "balance": 0, "txnTime":...

How to calculate MAX(SUM()) in postgresql in particular to query below

sql,postgresql,aggregate-functions
I am trying to get MAX(SUM()) in PostgreSQL, which is not possible directly in PostgreSQL. I am trying to get the result by any method, but unable to get the desired result. My table is like this - CREATE TABLE user_transactions ( id integer NOT NULL, user_id bigint, credit_rupee numeric,...

Cutomized function in $group operator of aggregation framework in MongoDB

javascript,mongodb,mapreduce,aggregate-functions
I want to use a customized $sum (lets call it $boolSum) function that returns the number of true element in an array. e.g. group :{ _id: { a : '$a' b : '$b', c : '$c' }, d1: { $boolSum : '$d1'} d2: { $boolSum : '$d2'} } But it...

Mysql nested count

mysql,count,aggregate-functions
I have 3 tables connected to each other booking > bid (booking_id) > bid_participants (bid_id) so when a booking is created, it will be blasted and received to more than 0 person or not received at all what i am trying to achieve is to count the booking if is...

How to Select the Most Occuring Items in SQL with Names

sql,sql-server,tsql,aggregate-functions
I'm trying to select the most occurring item. This code works, but I don't know how to get it's respective ItemName from the Item table without it erroring SELECT TransactionDetail.ItemID, COUNT(*) AS 'AmountSold' FROM TransactionDetail GROUP BY TransactionDetail.ItemID ORDER BY COUNT(*) DESC Attempted code: SELECT TransactionDetail.ItemID, COUNT(*) AS 'AmountSold' FROM...

how to show the variable name while aggregate in R

r,aggregate-functions
I was wondering is there any parameter setting when I do the aggregates as below , the result will show the origin column names instead the generic "group.1" data1 <- aggregate(mtcars[1:4], list(mtcars$am, mtcars$gear),mean) data1 Group.1 Group.2 mpg cyl disp hp 1 0 3 16.10667 7.466667 326.3000 176.1333 2 0 4...

How to change the value of sum function inside the query?

mysql,aggregate-functions
I have a query with sum aggregation function : SELECT sum(case when result=1 then 1 when result=2 then 0)as final_result From results I want to change this part when result=2 then 0 to something like that when result=2 then final_result equals zero Is it possible to do this ? or...

MySQL Why automatically sum when using aggregate function

mysql,sql,aggregate-functions
I'm trying to write a MySQL query that calculate Standard Deviation of a numeric column without using STD(). My working query is as below: SELECT sqrt(power((amount-avg(amount)),2)/count(amount)) FROM Orders; Please just treat 'amount' as the column name and 'Orders' as the table name. My question is why there is an automatically...

Find spectators that have seen the same shows (match multiple rows for each)

sql,postgresql,aggregate-functions,common-table-expression,relational-division
For an assignment I have to write several SQL queries for a database stored in a PostgreSQL server running PostgreSQL 9.3.0. However, I find myself blocked with last query. The database models a reservation system for an opera house. The query is about associating the a spectator the other spectators...

Subquery with the same WHERE clause as query

sql,postgresql,aggregate-functions,window-functions,aggregate-filter
In Postgres 9.4, I have a table which contains user's emails sent by different senders. I need to generate the list of the recent distinct senders, I'm using following query: SELECT DISTINCT ON (tableA.senderName) tableA.senderName,tableA.received,tableA.someOtherColumn FROM tableA WHERE tableA.received BETWEEN timeA AND timeB ORDER BY tableA.senderName,tableA.received DESC Since tableA contains...

Get last 7 days count even when there are no records

mysql,sql,laravel-4,aggregate-functions
How can I make a SQL query that returns me something like --------------------- |DATE | Count | --------------------- |2015/01/07 | 7 | |2015/01/06 | 0 | |2015/01/05 | 8 | |2015/01/04 | 5 | |2015/01/03 | 0 | |2015/01/02 | 4 | |2015/01/01 | 2 | --------------------- When there are...

Count rows for each unique combination of columns in SQL

sql,postgresql,aggregate-functions,greatest-n-per-group
I would like to return a set of unique records from a table based on two columns along with the most recent posting time and a total count of the number of times the combination of those two columns has appeared before (in time) the record of their output. So...

efficient way to double aggregation for a data frame

r,data.frame,aggregate-functions
I have a questions regarding to aggregation a data frame double times, and involves re-formate the table. I have a table, contains two columns: name, and category. The category is factor variable, contains 10 levels, say '0' to '9'. So the data frame looks like: name category a 0 a...

Issues with Group functions

sql,aggregate-functions,sqlplus
I'm working on lab question that I just cannot come up with a solution, that would allow me to figure out the books that have more then one author(See the posted question below for my comments to make sense). My mind is totally blank on it. I'm very bad with...

Multiple Datasets in one Table OR show distinct Value in Table (SSRS)

mysql,reporting-services,sum,aggregate-functions
I am fairly new to SSRS and currently I'm working on building my first reports. I encounter some problems with a report that in my opinion should be fairly easy, but so far I couln't find the right answer. I have a table similar to this: Material - - Date...

Aggregating in R

r,count,aggregate-functions
I have a data frame with two columns. I want to add an additional two columns to the data set with counts based on aggregates. df <- structure(list(ID = c(1045937900, 1045937900), SMS.Type = c("DF1", "WCB14"), SMS.Date = c("12/02/2015 19:51", "13/02/2015 08:38"), Reply.Date = c("", "13/02/2015 09:52") ), row.names = 4286:4287,...

Order by conditional count of associated records

sql,postgresql,sql-order-by,left-join,aggregate-functions
One quiz can have none, one or many submissions. Each submission has submissions.correct = t or submissions.correct = f. What's the best Postgres 9.4 query to get all quizzes (regardless of whether they have a Submission or not), ordered by the number of associated submissions with correct = t in...

mongodb distinct of groupby multiple keys

mongodb,group-by,aggregate-functions
I'm trying to query mongoDB to fetch data aggregation (group, match, last). Here is my document: { "_id" : 1, "from" : "a", "to" : "b", "message" : "a to b", "createdAt" : ISODate("2015-06-06T16:42:32.789Z"), "updatedAt" : ISODate("2015-06-06T16:42:32.789Z") } { "_id" : 2, "from" : "a", "to" : "c", "message" :...

mysql if inside select with aggregate functions

mysql,sql,performance,if-statement,aggregate-functions
I have an inventory table that lists quantities at multiple locations. I keep a quantity value and I now have to keep a "threshold" value, and when I reach the threshold stop selling the product. It's just a fudge factor so when people have inaccurate inventory there is a little...

SQL Server MAX and GROUP BY not playing nicely together

sql-server,tsql,group-by,max,aggregate-functions
I have a T-SQL query of the form: SELECT f.fizz_name, b.buzz_version, fu.foo_name FROM fizz f INNER JOIN buzz b ON f.fizz_id = b.fizz_id INNER JOIN foo fu ON b.buzz_id = fu.buzz_id WHERE f.bar LIKE 'YES' When I run this query I get the following results: fizz_name buzz_version foo_name ==================================== Gamma...

Linq grouping and nested grouping

c#,linq,aggregate-functions
I am in the process of creating a TreeView that can be displayed with various groupings. The essential classes are below: public class MachineStatus { public string MachineName {get;set;} public string Department {get;set;} public string LocationName {get;set;} public IEnumerable<DeviceStatus> Devices {get;set;} } public class DeviceStatus { public string DeviceName {get;set;}...

Sum by one field, but only for unique ID

sql,sql-server,distinct,aggregate-functions
I need to sum a sales quantity by month and year, but my database has some duplicate entries. I know this is not ideal, but it is a government database, so I cannot fix it. The database has a large number of records (> 200 million) so I don't want...

PostgreSQL: Aggregate by more than one column

sql,postgresql,aggregate-functions
I have the following data format: id eventtime use A 2015-01-01 00:00:00-01 5.2 . . 2015-06-06 23:59:50-01 5.7 B . . I'm trying get an output of the form: id eventtime use A 2015-01-01 1200 . 2015-06-06 1400 . B 2015-01-01 1500 basically sum of the item use for every...

MySQL query for aggregating terms over several conditions

mysql,aggregate-functions
Starting with data like the following: clubType desiredShape lineDirection --------------------------------------- driver straight left driver draw straight iron straight right wedge straight straight iron fade right wedge straight straight iron fade left iron draw straight I'd like to write a query that can return: count desiredShape = "draw" count desiredShape =...

Conditional Sum in SQL

sql,aggregate-functions
I am looking for a way to add create a summation in a select statement which is conditional on another field. This is best explained with an example. Suppose we have the following values in a table EmployeeID SalesQty Verified 0025 34 Y 0040 56 Y 0040 17 N 0040...

Merge two MongoDB aggregates into one pipeline

json,node.js,mongodb,aggregate-functions
I have a collection of population census and I still don't dominate the “aggregate” function to get the results with just one query. The collection has this format (plus a ISO 8601 timestamp). This way, each time a census is conducted we can register the current ages and counts (which...

Calculate the sum of a field filtered by a window defined on another field

sql,postgresql,aggregate-functions,aggregate-filter
I have table event: event_date, num_events, site_id I can easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY site_id. But I also have another table site: site_id, target_date I'd like to do a JOIN, showing the SUM of num_events within 60 days of the target_date, 90 days, 120 days,...

Counting and aggregating by two rows

sql,postgresql,aggregate-functions
Say I have a table like the following : id | key | user_id | value --------------------------------------- 8 | bp_diastolic | 1 | 93.0 7 | bp_systolic | 1 | 128.0 20 | bp_systolic | 2 | 108.0 21 | bp_diastolic | 2 | 76.0 35 | bp_diastolic | 3...

Generating report directly from Mysql Query data (using groupby, count)

php,mysql,sql,group,aggregate-functions
I have two tables for storing image and its related exif data: Image table has records like below: (query: select * from image_table where order_id = 3030303) Image_Exif_Info table has records like below: (query: select * from image_exif_info where image_id in (select image_id from image_table where order_id = 3030303) As...

Sql Server Multiple Joins with Aggregate

sql-server,left-join,aggregate-functions
I'm trying to fix a bug in inherited code. This query is meant to bring back an amex_meal_amount_total of $33, but it is giving $99. The problem is with the second join - there are three associated items in the EE table that is making the aggregate sum up three...

Mysql: Group by Hour, 0 if no data

mysql,sql,time-series,aggregate-functions
I have the following query: SELECT count(*) as 'totalCalls', HOUR(`end`) as 'Hour' FROM callsDataTable WHERE company IN ( SELECT number FROM products WHERE products.id IN (@_PRODUCTS)) AND YEAR(`end`) = @_YEAR AND MONTH(`end`) = @_MONTH group by HOUR(`end`) Above query returns only the hours in which there where calls made: totalCalls...

“aggregate” function inside of “ifelse” statement in R

r,aggregate-functions
I am trying to used "aggregate" function inside of "ifelse" statement in R. I want to return NA if min sale is equal to 0 then if not aggregate it. like the codes below: df <- data.frame(day=c("Mon","Tue","Mon","Tue","Tue","Tue"),sales=c(799,1359,1359,1359,1359,1359)) df2 <- ifelse(min(df$sales)==0,NA,aggregate(sales~day,data=df,sum)) My problem is that it return this: [[1]] [1] Mon...

SQL Count function - simple aggregate needed

sql,oracle,aggregate-functions
Consider the 2 columns below. How can I add the count of each name and place it on each row, as per the 2nd table below? name amount joe 5 joe 6 joe 7 jim 3 jim 4 to name name_count amount joe 3 5 joe 3 6 joe 3...

Get separate count for each condition within group

sql,oracle,group-by,oracle10g,aggregate-functions
I am trying to get a view of table information from and Oracle 10g table that lists the counts of specific values of a column in their own columns with each row being the group value. for example: The first select is : SELECT processed_by, count(priority) as P2 FROM agreement_activity...

An Efficient Summarisation of this SQL Query Solution

sql,select,aggregate-functions
I got this SQL query exercise from SQL-EX.RU Find out makers who produce only the models of the same type, and the number of those models exceeds 1. Deduce: maker, type Table info: Product(maker, model, type) PC(code, model, speed, ram, hd, cd, price) Laptop(code, model, speed, ram, hd, screen, price)...

SQL: left join and sum: Arithmetic overflow error converting expression to data type int

sql-server,aggregate-functions,outer-join
I have this query: select p.UserName, sum(b.PercentRials) as amount, sum(r.Amount) as Pays from bills b inner join UserProfiles p on b.PayerUserName=p.UserName left outer join PayReceipts r on p.UserName=r.UserName where p.[Percent]>0 and b.PayDate>'2014-11-20' group by p.UserName I get this error when running it: Msg 8115, Level 16, State 2, Line 1...

Optimal algorithm for segmenting set of integers into labels for a chart axis?

javascript,algorithm,plot,charts,aggregate-functions
Say you get values anywhere from 0 to 1,000,000,000, and you want to plot 30 days. So one particular chart may have a set like: [ 1, 465, 123, 9, ... ] While another chart can have a set with much larger numbers: [ 761010, 418781, ... ] Is there...

sum columns from two tables into third table with two columns

mysql,sql,select,aggregate-functions
I'm fairly new to MySQL. I'm trying to take a summarized data(float) from two columns in two different tables and create a third table with total of two columns and one row which is the sum total ... here is what I got so far: SELECT SUM(column_from_table1), SUM(column_from_table2) From table1,...

Elasticsearch: aggregation min_doc_count for weeks doesn't work

hadoop,elasticsearch,aggregate-functions
I've the following aggregation with interval=week and min_doc_count=0 { "aggs": { "scores_by_date": { "date_histogram": { "field": "date", "format": "yyyy-MM-dd", "interval": "week", "min_doc_count": 0 } } } and date filter from Jan-01-2015 to Feb-23-2015 { "range": { "document.date": { "from": "2015-01-01", "to": "2015-02-23" } } } I expected Elasticsearch to fill...

Postgres json_agg Limit

postgresql,aggregate-functions
I'm using json_agg in Postgres like this json_agg((e."name",e."someOtherColum",e."createdAt") order by e."createdAt" DESC ) But I want to limit how many rows will be aggregated into JSON. I want to write something like this json_agg((e."name",e."someOtherColum",e."createdAt") order by e."createdAt" DESC LIMIT 3) Is it possible in some way? This is full query...

SQL Average of Sum of Calculated Attribute

mysql,sql,join,group-by,aggregate-functions
I am trying to take the average of the values for a category, where the rows are grouped by sub-category with a calculated sum. The Primary key of the Parent Table is the grouped attribute of the Child Table. The grouped attribute of the Parent Table is neither the primary...

grouping data in mongo using json data stored

mongodb,grouping,aggregate-functions,aggregation-framework
This is the data I have in my mongo db: { "_id": ObjectId("556d1c7716efd4a035d8e473"), "products": [ { "gtin": 77770000222313, "gpc": 10000068 }, { "gtin": 77770000222312, "gpc": 10000068 } ] } How do I aggregate this so that I get gpc value and then an array under that of the gtins? Something...

Filter by count in elasticsearch

elasticsearch,aggregate-functions,aggregate
Consider the following data POST programming/languages/1 { "name":"java", "type":"general_purpose" } POST programming/languages/2 { "name":"javascript", "type":"scripting" } POST programming/languages/3 { "name":"c", "type":"general_purpose" } GET programming/languages/_search { "query": { "match": { "type":"general_purpose" } } } If I need to find the docs which has more than one match of the field type,...

SQL query to select the latest records with a distinct subject

sql,sql-server,group-by,subquery,aggregate-functions
I am using SQL Server and have a table set up like below: | id | subject | content | moreContent | modified | | 1 | subj1 | aaaa | aaaaaaaaaaa | 03/03/2015 | | 2 | subj1 | bbbb | aaaaaaaaaaa | 03/05/2015 | | 3 | subj2...

sql conditional aggregate function with date type

sql-server,aggregate-functions
I have table like: CREATE TABLE myissues ( id int IDENTITY(1,1) primary key, title varchar(20), status varchar(30), submitdate datetime, updatedate datetime ); INSERT INTO myissues (title, status,submitdate,updatedate) VALUES ('issue1', 'closed','2014-01-01 07:59:59.000','2014-01-02 10:59:59.000'), ('issue2', 'closed','2014-01-01 08:59:59.000','2014-01-02 12:59:59.000'), ('issue3', 'closed','2014-01-01 09:59:59.000','2014-01-02 10:59:59.000'), ('issue4', 'closed','2014-01-02 07:59:59.000','2014-01-03 10:59:59.000'), ('issue5',...

Select max record of each group on a group by

sql,postgresql,aggregate-functions,greatest-n-per-group
I'm using PostgreSQL. I need to select the max of each group, the situation is that the table represents the products sell on each day, and I want to know the top sold product of each day. SELECT sum(detalle_orden.cantidad) as suma,detalle_orden.producto_id as producto ,to_char(date_trunc('day',orden.fecha AT TIME ZONE 'MST'),'DY') as dia...

MysQL - Aggregate Datetime Row splitting

mysql,sql,datetime,aggregate-functions
Can some help me with the below MySQL challenge please ? The reported data is broken down by hour within the selected date. If a device_table indicates that the device is unavailable across several hours it will be reported within each of those hours, e.g. if device held records for...

% of total calculation without subquery in Postgres

sql,postgresql,aggregate-functions,percentage
I'm trying to create a "Percentage of Total" column and currently using a subquery with no issues: SELECT ID, COUNT(*), COUNT(*) / (SELECT COUNT(*) FROM DATA) AS % OF TOTAL FROM DATA GROUP BY ID; | ID | COUNT | % OF TOTAL | | 1 | 100 | 0.10...

Updating a table based on aggregation results from another table

mysql,sql,aggregate-functions
The idea for this is that I am trying to update salaries of Instructors differently based on if they're mentoring Freshman, Sophomores, Juniors, or Seniors. Only the highest applicable raise should be applied, which I have gathered is covered by the fact that case statements are evaluated sequentially. I am...

SQL-Server. Calculate formula with subquery in aggregate function

sql,sql-server,tsql,subquery,aggregate-functions
I need to calculate value of Quantity column where Name = A using this formula in SQL-Server: A(Quantity) = (B*B1 + C*C1 + D*D1) / SUM(B1 + C1 + D1) + E Sample data: Static Static Dynamic Name ID Quantity -- Here are more columns, but doesn't matter A 1...

Create sequence with data.table

r,data.table,aggregate-functions
I have a data.table of the format id | pet | name 2011-01-01 | "dog" | "a" 2011-01-02 | "dog" | "b" 2011-01-03 | "cat" | "c" 2011-01-04 | "dog" | "a" 2011-01-05 | "dog" | "some" 2011-01-06 | "cat" | "thing" I want to perform an aggregate that concatenates...

Sustract two queries from same table

sql,postgresql,aggregate-functions,subtract
Let's say I have 2 tables: table device is a set of measuring devices, table data is a set of values of different types measured by the devices. Table data = (no, id,value_type,value, dayhour) no is PK, id refer to table device Table device = (id, name) id is PK...

Postgres max on few columns

sql,postgresql,max,aggregate-functions
I have table, which looks like this id | item | price1 | price2 I want do get the list of items and for every item I want also to select max price among price1 and price2. Is it possible to do that in one query? Something like SELECT item,...

Select multiple row values into single row with multi-table clauses

postgresql,select,aggregate-functions,aggregate-filter
I've searched the forums and while I see similar posts, they only address pieces of the full query I need to formulate (array_aggr, where exists, joins, etc.). If the question I'm posting has been answered, I will gladly accept references to those threads. I did find this thread ...which is...

Return multiple rows when using AVG

mysql,sql,select,aggregate-functions
Why does this SQL query return only one result? select data_value-AVG(data_value) from data While the expected result is multiple rows with the difference to the average for each data_value....

How do you calculate a boolean aggregate over a column in BigQuery?

sql,aggregate-functions,google-bigquery
I have a table of events of users, and I want to project those events into a new column with some predicate, and then aggregate the events together per user into a new projection that tells me if a user has ever had the predicate match for them, or if...

SQL Server: avg rate for male and female employees in AdventureWorks

sql-server,aggregate-functions,adventureworks
I'm trying to write a query to find the average pay rate for male and female employees in the AdventureWorks database I wrote this (below), but I am not getting the desired results: with sub as ( select emp.Gender, emp.VacationHours, pay.Rate from HumanResources.Employee emp, HumanResources.EmployeePayHistory pay where emp.BusinessEntityID = pay.BusinessEntityID...

SQL divide data in rows

sql,aggregate-functions
I have the following table named Table: activity userid appid type 1 a imp 2 a imp 2 a click 3 a imp 4 a imp 4 a click 5 b imp 5 b click I am trying to calculate the click-through rate for each appid. In this instance we...

Create Subtotal Rows from Group but Remove the detail rows via TSQL

sql,tsql,group,aggregate-functions,subtotal
I basically have: select a,b,c,d,e from table1 I need the subtotal of e where c and d are the same. I suppose we can do a group by c and d but will run into a "..not part of aggregate function" issue for columns a and b are not in...

R Dataframe: aggregating strings within column, across rows, by group

r,string,data.frame,aggregate-functions,string-concatenation
I have what seems like a very inefficient solution to a peculiar problem. I have text data which, for various reasons, is broken across rows of a dataframe at random intervals. However, certain subsets of are known to belong together based on unique combinations of other variables in the dataframe....

Calculate percentage for single record in SQLITE

android,sqlite,aggregate-functions,percentage
I have Register table and in this I have following fields like: r_id , f_name , l_name , isactive , pkg pkg field can have values of 1,2,3,4. I want to find out percentage of how many user has pkg value 1 and 2 and 3 and 4. I used...

Expression Tree for Aggregate Function in LINQ to Entities

c#,linq,entity-framework,linq-to-entities,aggregate-functions
I need to dynamically substitute an aggregate function on top of a LINQ to Entities query to perform the operation in database and in the same time to do not hard-code what function do I use, e.g. replace Average() with Max() or Min() in a query like this: IQueryable<Order> orders...

SQL Server - Counting dates per item and arranging in groups

sql,sql-server,sql-server-2012,aggregate-functions
I have a query that outputs an ID and a Date, such as: [ID] [DateValue] A111111 2015-01-01 A111111 2015-01-02 A111111 2015-01-03 A111111 2015-01-04 A222222 2015-01-01 A222222 2015-01-02 A333333 2015-01-01 A333333 2015-01-02 A333333 2015-01-03 From this resultset, I need to determine one, two, and greater than two date groups that groups...