Combining multiple data points into a single data point. This usually is used in reference to SQL and usually to an aggregate function of some sort.

learn more… | top users | synonyms (1)

-1
votes
2answers
67 views

Calculating multiple medians

I have defined function with table value parameter which returns median as per values passed to it. Function defined as: CREATE FUNCTION [dbo].[fn_GetMedian](@List TypeMedian READONLY) ...
0
votes
1answer
14 views

aggregate on join latest value

This is a problem I often get stuck in and I hope there is a solution. Let's build an example: Table products: product_id, product_name Table Prices: product_id, valid_since, Price Lets say I ...
-2
votes
1answer
41 views

why unable to execute table-valued function?

Here is table-value function I have declared like: CREATE FUNCTION fn_GetMedian(@List TypeMedian READONLY) RETURNS INT AS BEGIN RETURN ( Select ( ( ...
4
votes
2answers
343 views

How can I pass column to function in sql?

I'm calculating median as: DECLARE @TEMP TABLE ( ID INT ) Select ( ( Select Top 1 ID From ( Select Top 50 Percent ID ...
4
votes
3answers
425 views

Avoid double query on the same table

I have two queries which are doing basically the same thing, but have different grouping, the first query (query 1) is used to populate a chart, and the second to populate a table. Query 1: SELECT ...
1
vote
2answers
62 views

Query on bridge table

I have three tables dealing with books: Authors, Titles of books written and a bridge table TitleAuthor, from from which I am trying to find all books (Titles) with more than one author. These are the ...
3
votes
2answers
43 views

Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do: I have a table, fields: id SERIAL inet INET ports integer[] id | inet | ports ----+------------+------------ 2 | 1.2.2.1 ...
4
votes
1answer
41 views

Group and count array elements using intarray

I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this: items ------------------------------------- id name tag_ids ...
3
votes
2answers
38 views

Get rows with minimum value

I have tables company: company_id | - - - - - - - - - - - - - - 1 | 2 | employee: employee_id | - - - - - - - - - - - - 1 ...
0
votes
0answers
22 views

How to pull sales for every single month based on my query below using ORACLE

So I am working on a report right now that pulls customer information, and sales for a particular date range, who used a PROMOTION CODE. My current query pulls all the data I need and gives me a sum ...
1
vote
2answers
46 views

Displaying a value associated with a maximum value

I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product ...
0
votes
1answer
101 views

Multiple criteria for including / excluding rows

CLARIFICATION UPDATE Unfortunately we report based on the 3rd party application and database so we cannot modify the datatypes since we do not own it. To help clarify, This report basically shows us ...
1
vote
1answer
31 views

Help to retrieve data set

I have data set as below, for each Parent Record there can be multiple Cancel and Un-Cancels. I am trying to pull for each Parent, I am trying to find when it when it got cancelled and when it got ...
5
votes
3answers
100 views

Aggregate discarding values in one column that haven't a match in another column

Say, I have a table representing colored and labeled items inside numbered boxes. Each box can not contain more than one item with a particular label, but items with the same label (and the same or a ...
0
votes
0answers
41 views

Aggregate function within CASE or Joins

Database - MySQL I have 3 tables Table1 - Order Columns -OrderId,OrderDate Contraints- PrimaryKey-OrderId Table2 - Product Columns-ProductId, InsertDate, ...
0
votes
2answers
58 views

Calculating Margin in CASE Statement and GROUP BY Clause

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement. I am working out Margin % and grouping into months using a case statement like so: SUM(CASE ...
0
votes
1answer
33 views

How to get first value from list of value

I have data as follows Id Col1 Col2 ================ 101 101 abc 101 102 def 102 102 ght 103 101 mgj 103 102 dkf 103 103 dfj I need output as follows Id Col1 ...
0
votes
0answers
29 views

Need help with Oracle Query for totaling fields with separate clauses, any feedback highly appreciated!

I am stuck on an issue that has been causing my mind to ramble for the last couple of days. DETAILS ABOUT REPORT This report is to identify the following details based off of the CUSTOMER_ID new ...
0
votes
0answers
29 views

limit aggregate function across join

I have a facts table, and each fact has one-or-more distributors in fact_distributors. A distributor is an entity, and for reporting purposes entities are grouped together into an entity_group via ...
1
vote
1answer
38 views

Extract start and end per group of rows, where only the end can be identified

I have a table that looks like this. id date_from date_to description vehicle 1 2015-10-21 08:00 2015-10-21 10:00 GARAGE TO CLIENT 1 1 2 2015-10-21 12:00 ...
0
votes
0answers
20 views

Joining one table with multiple tables for aggregate functions possible?

I am trying to get this seemingly simple looking query to work : SELECT c.id, c.name, count(chc.id) as contacts_count, count(cha.id) as addresses_count FROM (customers c LEFT JOIN ...
5
votes
1answer
193 views

Efficient merging (removing duplicates) of arrays

I have two tables, left2 and right2. Both tables will be large (1-10M rows). CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER); ALTER TABLE left2 ADD PRIMARY KEY (id,t1); CREATE TABLE right2( t1 ...
2
votes
2answers
115 views

Postgresql: Pattern match against array elements?

Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array ...
1
vote
1answer
39 views

Aggregate rows of type array

I need to aggregate rows with a single column of type INT[] into a single array of type INT[]. The following results in an error "could not find array type for data type integer[]": create temp ...
-6
votes
1answer
48 views

Minimum value per group [closed]

How do I get the min timestamp value of the same Id number using SQL? (please see below picture) Sample Table Sample Table of what should be the result
1
vote
2answers
133 views

Calculating Running Average using Over clause in SQL server

My table looks like this: CREATE TABLE [dbo].[TimeSeries]( [LOCID] [int] NOT NULL, [Date] DateTime, [YEAR] int, [MONTH] tinyint, [RTT] [int] PRIMARY KEY ( [LOCID] ASC,[Date] ASC) ) It contains 12 ...
1
vote
2answers
31 views

Selecting Values That Differ From Section Average

I'm attempting to select data from a table that's already been made containing records of file size as taken every twelve hours. The table looks something like this: surrogate_id | partition_id | ...
17
votes
3answers
392 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
4
votes
1answer
514 views

Select when group by contains more than one row with the same value?

I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many ...
3
votes
1answer
55 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
5
votes
2answers
193 views

Nested aggregation and grouping on multiple columns in MySQL

Consider the following product table (which is highly trimmed down): `id` int AUTO_INCREMENT `category_id` int `subcategory_id` int `vendor_id` int `price` decimal(6,2) `inserted_at` timestamp For ...
6
votes
2answers
84 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
1
vote
1answer
1k views

PostgreSQL: How to get a sum of values and the total rows that went into each value? [closed]

I have a table, MyData in the following format: |id|theme_id|priority_id|value| I am trying to get the following result for all the rows: theme_id | priority_id | total | sum ...
1
vote
1answer
57 views

Oracle How to Generate Multiple Ranges Based on a Result Set

Given a table like this: with qtys as ( select 3 as qty from dual union all select 10 as qty from dual union all select 9 as qty from dual union all select 7 as qty from dual union all ...
1
vote
0answers
59 views

MySQL Get first and last day of multiple records

I have a table HC_RSPI_data: | RADIO_TYPE | HOP | TOWARDS | RSPI | TP_NAME | DATE_TIME_END | TIME_LENGTH | RLTM_min | RLTM_max | ...
5
votes
1answer
2k views

Rolling sum / count / average over date interval

In a database of transactions spanning 1,000s of entities over 18 months, I would like to run a query to group every possible 30-day period by entity_id with a SUM of their transaction amounts and ...
3
votes
1answer
537 views

Avoid joining to the same table multiple times

I have two tables: CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4, c_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, t_id int4, pos int4 ); Table ...
3
votes
2answers
179 views

Filter and append data to row

My query and my data set (copied from sqlfiddle): CREATE TABLE one ( id int4 primary key, p_id int4, k_id int4 ); CREATE TABLE two( id int4 primary key, p_id int4, k_id int4, ...
2
votes
1answer
200 views

Why is ORDER BY LIMIT 1 so much faster than MAX?

Here are my two queries: select sts_in from sta_session where sts_user_id=2006 AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6' order by sts_in desc limit 1; And select max(sts_in) from ...
2
votes
1answer
90 views

How can I get hours worked totaled for a column?

I get good results until I try to perform calculations on an alias column. I am aware that that is not possible. However I need a breakthrough. Can someone lend a hand please :) I have to finish this ...
5
votes
1answer
63 views

Select a count with some conditions

Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum ...
1
vote
1answer
68 views

How to use PostgreSQL bit_or function to aggregate column?

I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this. Suppose I have bitwise flags in my column flags, and the ...
1
vote
2answers
296 views

PostgreSQL : avoid SUM over same values multiple times

I've a student table and a table of scores in various subjects. I want to sum score1 & score2 for each student across all subjects and then add the bonus for each student. CREATE TABLE student ( ...
1
vote
0answers
41 views

Best way to perform real time analytics on a growing collection of json file ( very large ) [closed]

I have a very large collection of JSON file (tens of millions) on S3 and I need to have some kind of aggregation (perform continuously as the collection grows). The aggregation is mainly counter ...
1
vote
2answers
75 views

Count of distinct values of a table using sql

name number ============= lin | 8 lin | 8 lin | 8 rob | 8 rob | 8 rob | 8 lin | 8 lin | 41 lin | 41 rob | 41 and I want the result like name | Count of 8 | count of 41 ...
3
votes
1answer
188 views

Decode and aggregate a BitMask in t-sql

I have a table that contains a bitmask field that stores permissions, where each bit denotes whether a particular permission is granted or not. Here's a simplified example: DECLARE @T TABLE (id ...
1
vote
1answer
43 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...
1
vote
1answer
28 views

Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

Say I have a table like the one described below. Table "public.foo" Column | Type | Modifiers ...
1
vote
4answers
86 views

Getting an aggregate (here: min) for a group of values [closed]

Example table: ID | value A | value B ---------------------- 1 | abc | 3 1 | def | 5 1 | ghi | 1 2 | cba | 9 2 | fed | 4 I want the ...
10
votes
3answers
380 views

Combining separate ranges into largest possible contiguous ranges

I'm trying to combine multiple date ranges that may or may not overlap into the largest possible contiguous date ranges. For example: Data: CREATE TABLE test ( id SERIAL PRIMARY KEY NOT NULL, ...