GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
1answer
21 views

MySql 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 histogram-type query to ...
0
votes
1answer
26 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
40 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
79 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
33 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
38 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
85 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
22 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 ...
-1
votes
0answers
30 views

SUM values within a grouped table

[MSSQL] Having a table like: --------------------------------- | A | B | C | D | --------------------------------- | 1 | ABC | 1.3 | 2.5 | | 1 | ABC | 2.7 | 2.5 | ...
0
votes
1answer
48 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
110 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
111 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
86 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
32 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
14 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
60 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
378 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
56 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
762 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
346 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
21 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
64 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
60 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
11k 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
14 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
49 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
1k 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
64 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
92 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
1answer
79 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
104 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
482 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
1k 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
106 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
57 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
32 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 ...
3
votes
4answers
4k 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 ...
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
0
votes
2answers
85 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. ...
1
vote
0answers
45 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
78 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
60 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
votes
1answer
86 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
1
vote
1answer
353 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 ...
0
votes
0answers
53 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
2answers
594 views

mysql - query records to fetch by time stamp

I've a MySql 5.6 DB with a simple table (MyISAM) . | UniqueKey | TimeStamp | ------------------------- | 1 | 1024125412 | | 2 | 1024125413 | | 3 | 1024125414 | My table ...
0
votes
0answers
43 views

Find all possible combinations for each group [duplicate]

I have a table like as: ________________________ |ID| Table1ID| Table2ID| |1 | 1 | 1 | |2 | 2 | 1 | |3 | 2 | 2 | |4 | 3 | 1 | |5 | 3 | 2 | ...
0
votes
2answers
56 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 ...
0
votes
1answer
67 views

How to group uninterrupted squences and get min max?

I have table where I am storing some sequential data that indicate occurrences of some event given day. id from_id to_id date 1 2 1 2014-08-01 2 2 ...