GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

0
votes
1answer
33 views

Grouping Based on Column Consecutive Values

I have table with let say the below 3 columns Date OfferId Title 4/29/16 - 5/19/16 123 title1 5/20/16 - 5/30/16 123 title2 5/31/16 - ...
0
votes
1answer
105 views

How to group by continuous days in mysql

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 ...
3
votes
2answers
49 views

Group by multiple columns with different members of the same hierarchy?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | ...
1
vote
1answer
51 views

1054 - Unknown column in subquery

i have 2 table one store data of relation user other buy user path table +-------------+---------------+-------------+ | ancestor_id | descendant_id | lenght | +-------------+---------------+--...
1
vote
1answer
17 views

Group by multiple column acquired from join

this is part of a query result : all three columns are user_ids acquired from multiple tables join . as you see each row has only one user_id and two other user_ids are necessarily null . I'm ...
3
votes
3answers
36 views

How to merge multiple query results into one

1. SELECT COUNT( * ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != '' GROUP BY prtner_name 2. SELECT SUM( inscan ) FROM tbl_shipment WHERE `awdno` != '' AND `prtner_name` != ...
1
vote
1answer
18 views

Multi-table sum of cost breakdown by pricing category

Looking for help with a query to get the sum of the subtotals for each price category from a list of line items, by order. Scratching my head on the group by stuff. There are 3 categories: setup, ...
12
votes
6answers
9k views

Find “n” consecutive free numbers from table

I have some table with numbers like this (status is either FREE or ASSIGNED) id_set number status ----------------------- 1 000001 ASSIGNED 1 000002 FREE 1 000003 ...
1
vote
0answers
43 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 // ... // ...
0
votes
0answers
15 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
21 views

SQL Error: Must be GROUPed or used in aggregate function

I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads: Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must ...
2
votes
1answer
41 views

Query 2 tables one with 1 row and the other with multiple rows

I have a part table: ID, Description ABC-123 ABC DEF-456 DEF I have a User Defined Fields table that stores information in a single row. ID, DOCUMENTID, STRING_VAL, DATE_VAL ...
0
votes
1answer
24 views

Full outer join issues when using group by [duplicate]

@@this may seem like a duplicate. The other post somehow posted as 'Guest' and will not allow me to make edits.@@ I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt ...
2
votes
3answers
86 views

Full Outer Join shows NULL when Group By is used

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query: select coalesce(co....
1
vote
1answer
63 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 ...
11
votes
4answers
6k 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
2answers
186 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 ...
1
vote
1answer
46 views

Slow MySQL query with Group by

I have a query that is taking a long time to execute minutes and more. The two tables orders and tickets have around 150k records. This is the query: SELECT orders.id, count(distinct tickets.id) ...
2
votes
1answer
78 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....
0
votes
4answers
65 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 ...
3
votes
3answers
7k views

Select columns inside json_agg

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 ...
1
vote
1answer
32 views

ORACLE: Changing the grouping of a data to be based on month breakdowns

To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy Here is a example of the script, and screenshots of the output file This is ...
3
votes
2answers
42 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
1
vote
1answer
22 views

PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is. now i want to calculate the percentile ntile(100) ...
0
votes
1answer
38 views

Grouping operator in DBMS (γ) is duplicate-impervious. Why?

I'm reading Database Systems by Ullman where its mentioned that γ is duplicate-impervious. I don't understand why a simple group operation should cause elimination of duplicates. Aliter, Duplicate-...
1
vote
1answer
129 views

Improving performance for sum and group by with large datasets

I have a query that looked like this: SELECT sum(score) as score, count(distinct(user_id)) number, round(sum(score)/count(distinct(user_id))) score_per_member, teams.*, types.name as type FROM `...
-3
votes
1answer
83 views

MySQL - GROUP BY clause recursively? [closed]

Under MySQL I can not group the three separate data columns via GROUP BY clause. This sample contains four brands, each brand has 2 models and 4 versions of engines. I need to extract 4 vehicles the ...
1
vote
3answers
79 views

Remove duplicates in SQL Server Group By

I have 2 tables, Product and Type, the tables looks something like this: Product -Id -Name -TypeId Type -Id -Name I want to be able to write a SQL Server query which outputs the products grouped ...
1
vote
1answer
23 views

Why does my PostgreSQL query cost so much when I tack on an additional group-by?

I need to extract two fields from a table. Here's the query with just one of the fields: set schema 'data'; explain (analyze, verbose) select count(example9), example9, ...
1
vote
1answer
45 views

Constraint for a unique column if other column has same value

I'm wondering if it's possible to have a column be unique for each value of another column in MySQL. What I'm wanting to have is the same functionality as what a GROUP BY would do. Here's the table: ...
1
vote
1answer
41 views

Grouping by more than 1 column using Partion By or any other method - Sql Server

I have the following tables in my SQL Server database: Product -Id -Name -Price -TypeId (Foreign Key) -SubTypeId (Foreign Key) Type -Id -Name SubType -Id -Name -TypeId (Foreign Key) I currently ...
0
votes
2answers
49 views

select only one element from an priority ordered group with limits?

This question searches for an answer in pure sql (no db-server specific code) -edit- Due comments/answers: "pure sql" - SQL92/99 because this works on all servers. I do not only use this code on ...
0
votes
1answer
24 views

Grouping in relational algebra with more than one grouping attribute

So I am having a bit of trouble understanding the Grouping operator, Ɣ. If had a the relation Sales(employeeId, date, salePrice) and I wanted to list the largest salePrice for each employee per day, ...
1
vote
1answer
31 views

Column must appear in the GROUP BY clause error even though it DOES appear there

I have a table that looks like this: order_number | key_a | key_b | key_c | key_d | quantity -------------|-------|-------|-------|------------------ 14400 | 2 | 3 | 4 | 54 | 40 ...
0
votes
1answer
23 views

Aggregate function to count occurrences of specific word

I have a table upon which I'm performing a GROUP BY statement. One of the columns in my table can contain one of 5 different words (essentially an enumerated type). Is there a way I can use an ...
0
votes
2answers
51 views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
0
votes
1answer
29 views

How do I figure TOTAL DURATION for specific group

I have a table with CARD # and REDEEMED DATE. I am trying to figure out the total duration (in minutes) for each CARD #. I am using Microsoft SQL Server 2014
0
votes
1answer
26 views

MySQL Using Group By, Need to add fields where answer is reliant on another fields data

I've asked a the same question before, but didn't get an answer and I believe I made the question too confusing to understand. BEFORE CODE: SELECT inventory.WHSE AS `Warehouse`, inventory.`CODE` AS `...
0
votes
1answer
191 views

MySQL code to show specific rows based on another…?

I'm having a hard time figuring out how to do this, and I can't explain it well. Since I don’t know how to explain it I can’t seem to locate an answer online by simple research. So, I have to draw it ...
3
votes
2answers
216 views

Different behavior between MySQL 5.5 and 5.6

I have this schema with data: CREATE TABLE `ticket` ( `id` INT NOT NULL, `name` VARCHAR(45) NULL, `type` INT(11) NULL, PRIMARY KEY (`id`)); CREATE TABLE `task` ( `id` int(11) NOT NULL, `...
3
votes
2answers
83 views

PostgreSQL: Generate a series of dates for each group in a table

I have a balances table in PostgreSQL 9.3 that looks like this: CREATE TABLE balances ( user_id INT , balance INT , as_of_date DATE ); INSERT INTO balances (user_id, balance, as_of_date) VALUES (...
0
votes
1answer
46 views

Remove duplicate rows from select query using group by

I have a situation to grab rows with a minimum value of a certain column. Below is the image of my made up table to explain what I am trying to do. I need to pull one record per instance_oid with ...
0
votes
1answer
64 views

Group by with limit

My question is regards the performance. What exactly happening when you use group by and then limit? I have a very big table, when I query with limit and without group by its immediate. When I use ...
2
votes
2answers
36 views

How to handle nested groupings with possible null elements in one query

As you can see in this sql-fiddle, I have three tables: events, event_dates, and event_times. The original tables contain various other fields, which I omitted as they aren't relevant, I just need to ...
1
vote
1answer
21 views

MAX and MIN for each car model

I have a problem with a query. I have a db with car and their spec, every model has a different car related to that model with its price. I want to create a query that gives me the min and max price ...
3
votes
2answers
66 views

Group only certain rows with GROUP BY

Schema I have the following set-up in MySQL database: CREATE TABLE items ( id SERIAL, name VARCHAR(100), group_id INT, price DECIMAL(10,2), KEY items_group_id_idx (group_id), PRIMARY KEY ...
4
votes
2answers
55 views

Query to get queue position for each group

I have a queue table with the following sample data: id company location 1 acme new york 2 acme philadelphia 3 genco st.louis 4 genco san diego 5 genco san francisco 6 acme ...
3
votes
3answers
352 views

How to select distinct TOP but group by column

I have a result set of 500 rows, but this are for 100 invoice. I need only the top 10 of the last invoices. I found something like similar: How to select top 10 records from each category Sample ...
1
vote
1answer
26 views

right join leaving one row from right table

I am using MariaDB,in my sample database I am having two tables department and emp. I want to display all the department having less than 50 employees. In this case it should display all the ...
3
votes
1answer
167 views

How to fetch data that is not similar

I have this table: | id | user_id | status | | a | 1 | p | | a | 1 | p | | a | 1 | np | | a | 1 | pp | | b | 2 | np | | b | 2 | np |...