GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
1answer
20 views

Optimization for aggregation and grouping with MySQL

I have an application that is pulling data on a daily basis from numerous APIs, storing it and then presenting it on the front end in various charts, tables, etc.. I'm having some issues avoiding ...
0
votes
1answer
27 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
0
votes
0answers
51 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
36 views

I want to rewrite my MySQL query so that FIND_IN_SET doesn't do a full table scan unless necessary

I want to rewrite my MySQL query so that the last table join doesn't do a full table scan unless necessary. Here's what I mean. Under certain conditions I don't need to select any rows in that join ...
0
votes
1answer
22 views

Group table records by Item than Year

I have a SQL table that is layed out as follows: {Itemcode} {FiscalYear} {FiscalMonth} {StartingQty} {PeriodChangeQty} Ball 2013 01 15000 -7500 Ball 2013 02 0 1000 Ball ...
3
votes
0answers
55 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
1answer
14 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
2answers
59 views

Optimizing an AVG / GROUP BY query

Here is the query: SELECT p.domain_id AS comp, AVG(IFNULL(p.position, 50)) as AVG, p.date AS period FROM userdomain ud LEFT JOIN userdomainexpression ude ON ude.userdomain_id=ud.id ...
1
vote
1answer
36 views

MySQL Update a table based on the last data from another table while grouping with conditions

I need a query to UPDATE a series of fields based on another table Table lp_plates_backup | uid | brand | model | | 1 | | | | a | Old | Error | ...
0
votes
1answer
51 views

Select on multiple tables with group by

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this: | network | supplier | total topups (EUR) | 1st topup qty| | ...
0
votes
0answers
29 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
1answer
44 views

Group by without temporary table and filesort

My aim is to make the following query faster. SELECT SQL_NO_CACHE Links.id,Links.link,sum(ID_Links_Words.count) AS wordCount FROM Links LEFT JOIN ID_Links_Words ON Links.id = ID_Links_Words.link ...
0
votes
2answers
43 views

Grouping rows by looking at two columns without considering the order and summing each separately

I have a table looks like this one below: +---------+---------+-------+ | SOURCE | DEST | VALUE | +---------+---------+-------+ | A | B | 1 | | B | A | 2 | | A ...
0
votes
3answers
41 views

Group by transformed column

I have a database with creations and orders, and a bunch of tables linking them. I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a ...
0
votes
1answer
24 views

Filter mysql group by negative having contains

How can I get all of the values from 1 column, which do not (or do) have a specific value in one of their rows? For instance, consider example table: | ColA | ColB | --------------- | aaa1 | zzz1 | ...
0
votes
1answer
49 views

GROUP BY on two large joined tables

I have two tables: Person ------ PersonID (PK) GroupID DeletedDate PersonData ---------- PersonID (PK) FieldID (PK) Value The fields are custom-created; hence the schema. I am trying to run a ...
0
votes
1answer
31 views

Multiple records and determine if a combination if values exist and write this to a new column

I'm struggling with a problem. An order can have multiple lines and accordingly a type (F=Footwear/N=Non-FTW,Mixed=Both) I need to determine if the order consists of which type and create a new ...
1
vote
0answers
59 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). ...
0
votes
1answer
43 views

Sum of a column that is already a sum

This is my query: select d.department_id, d.department_name, sum(e.salary) AS total from department d inner join employee e on d.department_id=e.department_id group by ...
0
votes
1answer
23 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 ...
0
votes
1answer
53 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 ...
2
votes
1answer
50 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
0
votes
3answers
288 views

Getting around the constraint “column must appear in the GROUP BY clause or be used in an aggregate function”

I'm using Postgres, which enforces the constraint that all columns in a SELECT...GROUP BY must appear in the GROUP BY clause or be used in an aggregate function. Lets say I'm modelling peoples' cars, ...
0
votes
1answer
34 views

Inner join and group by [closed]

So I've got two tables. Accounts has three columns: id, Account_ID, Account_Name Tickets also has three columns: id, Account_ID, Description I can use the following query to get the most ...
0
votes
0answers
21 views

MySQL GROUP BY performance not improved by 1-column vs 2-column condition

mySQL 5.6.22 on 32GB RAM CentOS I was very surprised that this change did not improve GROUP BY performance. It's a query with multiple joins, of course run with SQL_NO_CACHE option. In my original ...
3
votes
1answer
81 views

MERGE a UNION query results down to one row per KEY value

This is a lengthy query, it polls one LIVE database for certain sales information, then a UNION runs the same exact query on the ARCHIVE database for the same information. The results show each ...
1
vote
1answer
1k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
0
votes
1answer
86 views

select values from a table where column values should be different [closed]

Let us say there are 4 columns in a table . cpt,cpt4mod,itemcharge,eff_date. condition1: if the values of cpt,cpt4 and itemcharge are same, but eff_date is different. Then I need to fetch the ...
7
votes
3answers
1k views

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
0
votes
0answers
34 views

Sum of nested group query

I'm very new to SQL, so please bear suggest improvements anywhere. I have a PaymentsReceived table like this (simplified to only what is relevant) AccountID refrerences Accounts(ID) PaymentDate ...
2
votes
1answer
107 views

How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. CASE 1 -- GROUP BY and CASE SELECT SQL_NO_CACHE m.id, m.sku, ...
0
votes
1answer
65 views

GROUP BY Returning Different Totals [closed]

I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query ...
0
votes
1answer
16 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) - ...
1
vote
1answer
75 views

Limit distinct count of group by

Suppose for simplicity that I have a table t with columns id and value. I can find the ids that have more than one distinct value using a query such as SELECT id, COUNT(DISTINCT(value)) AS count FROM ...
0
votes
2answers
67 views

Find the last visit by teacher and show the rest of columns in MySql

I would like to group the teachers by the last visit and show the other values corresponding to the last visit. CREATE TABLE IF NOT EXISTS `visits` ( `FIELD1` int(5) NOT NULL, `teacher` varchar(9) ...
1
vote
1answer
113 views

How to get the SUM between dates in a WHERE clause?

I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example: Query: SELECT ...
0
votes
1answer
197 views

Oracle Nested Query with Group By and Having Clauses

So here's the question Find the IDs of aircraft which have flown more distance for a destination than the average distance of all flights for that same destination Here is the schema ...
0
votes
2answers
632 views

Error Grouping By Multiple Columns using CASE

Is it possible to group by multiple columns in a CASE statement? I get error "Incorrect syntax near ','." in below code... SELECT CASE WHEN Type = 'Test' THEN Description ELSE ...
0
votes
2answers
3k views

Grouping data by MONTH on DATETIME column in SQL Server

I have a table Employee which has the columns Id, Date of joining and Name Date of joining is a DATETIME column. I want to know how many users have joined in the month of October?
2
votes
1answer
186 views

Breaking down by minutes in between two dates

In MySQL, I've got a data model with a "visit" table that has start and end datetimes. How can I get a breakdown of the number of visits that are active per minute? e.g. | visit_id | start | end ...
0
votes
0answers
31 views

“DISTINCT” JOIN in mySQL [duplicate]

+----+-------------+-----------+---------+ | id | login_ip | hostname | user_id | +----+-------------+-----------+---------+ | 1 | 192.168.0.4 | 'host 1' | 1 | | 2 | 192.168.0.4 | 'host ...
1
vote
1answer
66 views

How group by affects aggregate functions

I tried to understand the following scenarios: Table name: family +------+-------+ | id | name | +------+-------+ | 1 | alice | | 2 | bob | +------+-------+ That table will be used for ...
0
votes
0answers
42 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 ...
1
vote
1answer
164 views

How to optimize a very slow query with joins and group by?

I have this GTFS database : All the ***_id columns are indexed, and there are foreign keys between tables (such as a FK on trips.route_id to routes.route_id). So basically the link between a stop ...
0
votes
1answer
21 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
1
vote
0answers
54 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
96 views

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones?

I'll base this question on a toy example. Let this be table A: A ------------------------- U | V | W | X | Y | Z ------------------------- a | b | c | 1 | 6 | 8.3 a | b | c | 1 | 4 | ...
0
votes
2answers
94 views

Group by + Order by Behavior Explanation? [duplicate]

Suppose I have: Product(_id,Name) Provider(_id,Name) Provides(ProductId, ProviderId, Price) If I want to obtain the providers who sell the most expensive products, and whose name starts with A. ...
0
votes
2answers
68 views

Need to group by column while ignoring some text format

I have the following data. Sport -------------- Baseball Football Baseball [a] Football [a] [b] Basketball How can I group this to ignore the data in the brackets?
1
vote
1answer
515 views

Group by age range and gender with aggregation function on some columns

In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get ...