GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

1
vote
1answer
26 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | ...
1
vote
1answer
28 views

Query with group by having count 0

I am working on application for managing football league as my homework. I want filter list of players by number of matches they have played in league. For example user can enter count > 0 and count ...
0
votes
0answers
23 views

not a GROUP BY expression [on hold]

I am not getting answer of this query SELECT DISTINCT HPD_Service_Type.owner_support_company,HPD_Service_Type.owner_support_organization, HPD_Service_Type.Service_Type, ...
-1
votes
1answer
29 views

Group by function not working correctly in SQL server CTE [closed]

This is SQL Server CTE, ;with grp (Sdate,TransactionType,tot) as (select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type], (count ([Transaction_type])) as [Total Count] ...
1
vote
1answer
66 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), ...
1
vote
1answer
19 views

select group by with multiple AND conditions on the same columns

I've an sql table like this: SID|SERVICE_SID|ATTRIBUTE_SID|VALUE 1 2 5 "VAL1" 2 2 4 "VAL8" 3 2 3 "VAL4" 4 11 5 ...
0
votes
1answer
23 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
61 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
42 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
59 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
2answers
60 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 ...
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: ...
1
vote
1answer
40 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
53 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
32 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
53 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
2answers
58 views

Group by reversible pair

I'm sure my title is not clear enough... Let's say I have a "chat" table, storing individual messages like this: message_id, from_user_id, to_user_id, timestamp For a given user_id, I want a list ...
0
votes
3answers
45 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 ...
1
vote
1answer
33 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
50 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
32 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
69 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
2answers
85 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
1answer
47 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 ...
2
votes
1answer
54 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
1answer
86 views

sql server Group by with calculated values

I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated ...
0
votes
1answer
27 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
56 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
125 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
1
vote
1answer
181 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
3answers
386 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
23 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
88 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
103 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.
5
votes
2answers
369 views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
0
votes
0answers
41 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 ...
0
votes
1answer
66 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 ...
2
votes
1answer
132 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, ...
-2
votes
1answer
13k views

How to get MAX and MIN in GROUP BY query?

In a simple query SELECT col1, col2, col3 FROM table1 GROUP BY col1 each row includes the first value of col2 and col3 for each unique value of col. How to select MAX and MIN for each value of ...
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
83 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 ...
2
votes
2answers
2k views

PostgreSQL : select columns inside json_agg

If 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
2answers
68 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) ...
0
votes
0answers
97 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 ...