GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
4answers
87 views

MySQL: Delete all rows older than 30 days, but only if count more than one

I need to cleanup my table of over 14.000.000 rows inside. I want to delete all entries, that are older than 30 days, but only if number of grouped items is larger than one. So, at least one item will ...
1
vote
2answers
88 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 ...
1
vote
2answers
95 views

GROUP BY multiple columns

I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date. The table is as follows CREATE TABLE `channel_stats` ( `channel_id` int(10) unsigned ...
0
votes
2answers
191 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 ...
4
votes
1answer
59 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, Data_3....
2
votes
1answer
94 views

Oracle Min - ORA-00937 not a single-group group function

I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command; SQL> select tickets.bus_id bus, tickets.fare fare, route....
1
vote
1answer
30 views

Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

This question is related to the following 2 SQL errors (line breaks added by me) MySql.Data.MySqlClient.MySqlException (0x80004005): Expression #2 of ORDER BY clause is not in SELECT list, references ...
1
vote
1answer
51 views

Keep one row per ID while keeping previous order (from ORDER BY)

I'm actually working on a big query and stuck at the very end. I'm working on MySQL 5.7. ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n 1 // 2 // x // y // ... // ...
1
vote
1answer
58 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 ...
1
vote
1answer
87 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than O(...
0
votes
1answer
13 views

calculate average of a class where students offer different number of subjects

I am trying to calculate the class average of a class where students can hold different amounts of subjects (e.g.: two students in a class of which one takes 3 subjects and the other 2). How do I get ...
0
votes
1answer
36 views

Order by reduces performance of a grouped result set

I'm trying to optimize a query which uses a combination of GROUP BY and ORDER BY which together cause a big performance hit (~x15). If I remove either of the conditions, the query runs pretty fast. I ...
0
votes
1answer
18 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
0
votes
1answer
30 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
0
votes
1answer
13 views

Ratio per Group in PostgreSQL

If I have rows like this: lec1 major1 class1 10 lec1 major2 class1 40 lec1 major1 class2 30 lec1 major3 class3 35 lec2 major1 class3 15 lec2 major3 class3 10 And I need it to combine the ratio into:...
0
votes
1answer
26 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
1answer
84 views

How to do a 'SUM' of a COUNT(*) in your select to be grouped by a particular column in SQL SERVER

What I am looking for is to be able to SUM the open_lines and group them by that so instead of 'cntnr_id' having open_lines showing as '1' it should show '2' since it is the same cntnr_id and ...
0
votes
1answer
49 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
0
votes
1answer
56 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
0
votes
1answer
38 views

How to remove some data not needed on new row from mysql query with rollup

Hello I have a problem with the rollup mysql query. I want to get total of each column. I use mysql SUM() and ROLLUP to get the result. But on the new row that is added it copies the data of the last ...
0
votes
1answer
18 views

GROUP BY parentId and MAX childId

My tables are below: crm_conversions crm_messages_of_conversions crm_receivers_of_messages I want to get all of conversions of an user with only last message. I'm trying to get with the query: ...
0
votes
1answer
96 views

Getting sum result for last year divided quarterly

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks. Also, if it is possible, I want to have the type as a separate column with the type as ...
0
votes
1answer
47 views

Filter and then sort products by availability

I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times. products - id_productSupplier (primary key) - id_product (foreign key) - ...
0
votes
1answer
32 views

MySQL order of query

My query return like this. 12 14 14 99 78 78 54 I want result to be sorted so that no same number comes consecutively in the result. The result that I want is like this. 12 14 99 14 78 54 78
2
votes
0answers
236 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
1
vote
0answers
46 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 ...
1
vote
0answers
269 views

Simple avg query on large table much slower in PostgreSQL than SQL Server

I have a database with three tables items, parameters and measurements in both servers and want to query the measuerment table. But the query is much slower in PostgeSQL (9.4) vs SQL Server (2012). ...
1
vote
0answers
571 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
1
vote
0answers
745 views

is it good to add index on column used in group by clause?

I have a query like- select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc; will it be faster if I am going to add index on x?
1
vote
0answers
117 views

can this mysql query be optimized further?

I have this query select counterparty_id, max(id) max_id from client_administrations where counterparty_type = 1 and effective_on <= '2013-10-22' group by counterparty_id When I ...
1
vote
0answers
387 views

MySQL GROUP BY and ORDER BY giving inconsistent results

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
0
votes
0answers
30 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
0answers
27 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 ...
0
votes
0answers
24 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 ...
0
votes
0answers
54 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 ...
0
votes
0answers
29 views

Problems Calculating and Grouping Results Accurately with timediff

Query Function time_to_sec (TIMEDIFF(ohrm_attendance_record.punch_out_user_time,ohrm_attendance_record.punch_in_user_time))/3600 AS time_to_sec_TIMEDIFF_ohrm Results should total to 4.35 Right? but ...
0
votes
0answers
43 views

Get last updated date and group by in mysql

trans_id | item_id | price | last_updated 01 |001 | 100 | 2015-01-01 02 |002 | 200 | 2015-01-02 03 |001 | 150 | 2015-01-03 03 |003 | 200 | 2015-01-...
0
votes
0answers
142 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
0answers
133 views

MySQL - Group By - How does it determine which row to return from joined table

I am getting desired, but inexplicable behavior on a particular query, and wanted to get more information before pushing up. I have 3 tables: foo bar bar_image And paring back all distractors, ...
0
votes
0answers
61 views

Mysql sum of column group by question

I have the results of a select that I am joining on that I need to get a slightly complex sum of a column from. I need the sum of result grouped on id PLUS the sum of result of any row where ...
0
votes
0answers
129 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT '...
0
votes
0answers
62 views

Is it possible to group by a fraction of the result set?

I have a database of products with prices. What I'd like to be able to do is figure out 5 different price ranges such that each range, when ordered by price, will have approximately the same number of ...
0
votes
0answers
240 views

How can I number my groups using an Access “Find Duplicates” Query?

I have a query created by the MS Access 2010 find duplicates wizard. It is: SELECT * FROM fbExpenses WHERE [expense_date] In (SELECT [expense_date] FROM [fbExpenses] As ...