GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

3
votes
1answer
5k views

GROUP BY and ORDER BY problem

I have two tables like this: CREATE TABLE cmap5 ( name varchar(2000), lexemes tsquery ); and CREATE TABLE IF NOT EXISTS synonyms_all_gin_tsvcolumn ( cid int NOT NULL, -- ...
1
vote
2answers
26 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
0
votes
1answer
50 views

Is there a way to used grouped data multiple times in a query

Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments. The solution that I ...
-1
votes
1answer
18 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
4
votes
1answer
56 views

Pull more records based on IDs in comma separated list returned by GROUP_CONCAT [duplicate]

Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, ...
7
votes
2answers
10k views

How to select multiple columns but only group by one?

I have a problem with group by, I want to select multiple columns but group by only one column. The query below is what I tried, but it gave me an error. SELECT ...
2
votes
2answers
29 views

List all articles with their amounts at a certain date+time

I've got a table with articles, and their stock amount changes: TABLE stock_history: +------------+---------------------+--------+ | article_id | amount_at | amount | ...
0
votes
1answer
66 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
-4
votes
0answers
75 views

how to get one of the values in group by?

For the following tables stock_moves product_id purchase_line_id state product_uom_qty ----------------------------------------------------------- 4018 1230 assigned ...
2
votes
1answer
56 views

How to get number of active users with one SQL-Query?

I am running a site similar to craigslist where users can create classifieds and write/reply messages. In order to get an idea of activity I would like to get the amount of active users per month. ...
0
votes
2answers
156 views

sqlite group_concat error while executing query

I have table: CREATE TABLE www ( Col_1 VARCHAR, Col_2 VARCHAR ); select count(*) from www; --1496 And I have error: "Error while executing query: aggregate function are not allowed in ...
6
votes
2answers
108 views

MySQL group by like statement?

I need to get some stats out of our MySQL-DB and wonder if there is a faster way in doing this. Basically I need info about user registration for any given month before including seperation by ...
1
vote
1answer
44 views

Combining GROUP BY and JOIN

This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the ...
4
votes
1answer
67 views

Improving sort performance in GROUP BY clause

I have two tables in Postgres 9.4.1 events and event_refs with the following schemas: events table CREATE TABLE events ( id serial NOT NULL PRIMARY KEY, event_type text NOT NULL, event_path ...
1
vote
0answers
26 views

Grouping and Transposing Rows to Columns in PostgreSQL

I have the follwing situation: I have profiles and tasks. One profile can be used once for each task. I track usage in profiles_used table. I need to build a report that shows how many profiles of ...
0
votes
2answers
36 views

Order By Case String Not Working

I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I ...
0
votes
1answer
71 views

Improving performance for sum and group by with large datasets

I have a query that looked like this: SELECT sum(score) as score, count(distinct(user_id)) number, round(sum(score)/count(distinct(user_id))) score_per_member, teams.*, types.name as type FROM ...
1
vote
1answer
41 views

Unknown column 'actions_in_group' in 'field list'

I keep getting: Unknown column 'actions_in_group' in 'field list' Here's the SQLFiddle: http://sqlfiddle.com/#!9/4ddf0/19 SELECT *, SUM(actions_in_group) AS total_rows_in_group, ...
0
votes
1answer
27 views

Optimising a query with two aggregate fields

I have a table in a MySQL 5.5 database with around 7 million records, with fields typeId, regionId, scanTime, scanId and a query that returns around 239,000 results. For every combination of typeId ...
3
votes
1answer
959 views

How to use a group by in inner join

I have Two tables: Table 1 ID totalamount --- ----------- 100 1000 101 500 Table 2 ID Individualamount --- ---------------- 100 500 100 300 100 100 101 200 101 300 I Need a result ...
0
votes
1answer
31 views

SQL query help: Calculating days between observations

I'm extremely new to SQL, in fact, I just really started playing around with it. So I'm not exactly sure how to search for appropriate terms related to the question I have. I'll do my best. ...
0
votes
1answer
21 views

Fetch column data against group by data

The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image. Now I need balance of ...
0
votes
2answers
57 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 ...
3
votes
2answers
5k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
1answer
39 views

Using group by on several tables with problems [closed]

I have a poorly-designed table in an Oracle database like this: sr: id an_rap 21 2015 rap: id cf 28185 123-123 ch: id fk_id_rap FK_ID_SR 12348 28185 21 dma: ch_id ...
0
votes
1answer
22 views

How can I group two joined tables

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL fk_id_chest 18059 18.56 R12 1 18060 11 R10 2 18061 15 ...
0
votes
1answer
29 views

How can I group (by) two joined tables [closed]

How can I group (by) two joined tables (in an Oracle database) like this: cant: id CANT_VAL COD_VAL 18059 18.56 R12 18060 11 R10 18061 15 R11 col: FK_ID_COL_DMA ...
4
votes
1answer
66 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...
4
votes
1answer
54 views

Adding a second Group By column slows query down

I have the following query: SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE' UNION ALL SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, ...
0
votes
1answer
32 views

Update rows in a table with an index column starting from 0 with consecutive numbers

I have a few tables in a Oracle db with some "rule": The INDEX_COLUMN must start from 0 and must be consecutive numbers and the column NRCRT must start from 1 and also consecutive numbers. I've made a ...
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 ...
1
vote
0answers
34 views

Find the last record of group of overlapped records

I have a little dilemma with filtering out result. The result and what I want to achieve is not very trivial and I didn't find an answer anywhere after extensive search. So here is my problem: I ...
0
votes
2answers
48 views

sql problem with multiple linked tables

I have an Oracle db and the following tables: raportor: id id_pct_lucru 58382 327342 ses_rap id an status 1 2012 I 21 2013 I 41 2014 D ch_trat id fk_id_raportor ...
-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
-3
votes
1answer
61 views

Finding the rows with the minimum value per group [duplicate]

How do I get the min timestamp value of the same Id number with its corresponding Value using SQL? Would anybody help me with this problem? (Please see attached image below) Sample Table Sample ...
0
votes
0answers
17 views

Query segment of database based on overall range of columns?

Purpose We're running a bunch of re-engagement experiments where we send different emails to our users and test which are most effective. In order to test these different emails we can't just send ...
1
vote
2answers
76 views

Multiple COUNT fields, GROUP BY

I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries? I'm aware of WITH ...
1
vote
1answer
139 views

2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Ref : http://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql I got answer (thanks coytech ) but i need one more column in its : as per : id | mid | pid | owgh | nwgh | ...
0
votes
1answer
41 views

Selection of first and last in non grouped item in SQL Server [closed]

Here's my table structure Id ToolName ToolKey ToolStatus ----------------------------------- 1 XXXX 123 Running 2 XXXX 123 Completed 3 yyyy 134 Running ...
5
votes
1answer
153 views

Is it possible to get seek based parallel plan for distinct/group by?

An example from this question shows that SQL Server will choose a full index scan to solve a query like this: select distinct [typeName] from [types] Where [typeName] has a non-clustered, ...
0
votes
0answers
48 views

MySQL: How to count non-unique rows in a multiple criteria COUNT query?

I want to calculate, in one query, the percentage of researchers (chercheurs) that have co-signed written publications (between two dates, only for a type of publications and only for researchers ...
2
votes
1answer
68 views

GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

Tables: content tag_map extrainfo |id|title| |id|tagid| |id|lat|lng| |--|-----| |--|-----| |--|---|---| |1 |Lorem| |1 |8 | |3 |0,4|0,1| |2 |Ipsum| |2 |9 | ...
4
votes
3answers
118 views

Return even empty groups when grouping by date

I have the following query: SELECT DISTINCT COUNT(CD_BarCode) ,CD_Carrier ,SUBSTRING(CONVERT(VARCHAR,DT_Arriving,103),1,11) Date FROM TB_AGIL WHERE ...
4
votes
1answer
481 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 ...
6
votes
4answers
8k views

Slow query on large table with GROUP BY and ORDER BY

I have a table with 7.2 million tuples which looks like this: table public.methods column | type | attributes ...
2
votes
2answers
36 views

Grouping in long tables

My application is running over Postgresql 9.4 and I found a problem and I don't know how can I fix it. I'm runing VoIp application, and we have a few tables that are too long (>60M of rows) with all ...
1
vote
2answers
63 views

group_concat in separate columns

I have a players table id name -- ---- 1 jack 2 john 3 mike 4 pikachu And I have a matches table where these players can be matched together. id pair_id player_id -- ------- --------- 1 ...
0
votes
1answer
23 views

How do I figure TOTAL DURATION for specific group

I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014
2
votes
1answer
196 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
0
votes
1answer
25 views

How can I get a single row on a inner join not to double my sum

I have two tables (DEP_DEZ & CAR_EXP): ID FK_ID_CHESTIONAR TOTAL_E TOTAL_F TOTAL_STOC 261 644 87 152 48 ID MASA_TOTALA FK_ID_CHESTIONAR 405 20 644 406 45 ...