GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
1answer
18 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
36 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
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 ...
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 ...
2
votes
1answer
37 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 ...
-2
votes
0answers
39 views

Multiply operation/field causing the record set to 'ungroup'?

I'm trying to sort a query for a report but every time I add a Multiply operation to it the record set result becomes a mess and if I try to GROUP BY I get the legendary ...either an aggregate ...
0
votes
3answers
84 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
31 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
334 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
753 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
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 ...
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, ...
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 ...
0
votes
1answer
13 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
47 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
63 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
76 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
101 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
476 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 ...
1
vote
1answer
107 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
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
84 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
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
vote
1answer
345 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 ...
1
vote
2answers
78 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 ...
-1
votes
1answer
85 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 ...
0
votes
0answers
52 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
581 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
54 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 ...
1
vote
1answer
530 views

Pivot table with MySQL

I'm trying to create a pivot table like output (cross table? Not entirely sure about the difference). The idea is to have rows, columns and summary values (specifically: distinct count). The data ...
0
votes
1answer
59 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
2
votes
1answer
99 views

Finding rows with duplicate values

I have a one to many relationship between the Account table and a ScheduledPayment table. One account can have many payments but there should be a maximum of one payment per day. Although unlikely, ...
0
votes
1answer
37 views

Is there any performance difference between a group by statement and nested select statements in sql?

I am curious to know about the differences between using group by statement and nested selects to achieve the same result. for example consider I have two tables such as the following: tblPurchase ...
1
vote
1answer
111 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
0
votes
1answer
133 views

How to query group for increase or decrease in value

In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last ...
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 ...