GROUP BY : SQL Aggregation for Common Values

learn more… | top users | synonyms

3
votes
2answers
52 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
18 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
38 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, ...
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 // ... // ...
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 | +-------------+---------------+--...
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
36 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
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....
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
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 ...
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) ...
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 ...
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-...
-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
1answer
24 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 ...
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 ...
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 ...
2
votes
1answer
79 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
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
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
217 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 ...
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 ...
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 ...
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 |...
0
votes
1answer
27 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
6
votes
1answer
122 views

Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name. ...
0
votes
1answer
12 views

Ratio per Group in PostgreSQL

If I have rows like this: lec1 major1 class1 10 lec1 major2 class1 40 lec1 major1 class2 30 lec1 major3 class3 35 lec2 major1 class3 15 lec2 major3 class3 10 And I need it to combine the ratio into:...
-3
votes
1answer
27 views

Postgresql Group by [closed]

I created a table in postgresQL like Now I ran a query like:: SELECT name FROM emp.company GROUP BY name And I am getting output like I am unable to get it whats exactly happening over here.
1
vote
2answers
39 views

Correct way to write this view? Total sales/purchases/profits by day

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables: transaction: idtransaction (pk) user_iduser (fk) ...
-1
votes
1answer
24 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
0
votes
1answer
57 views

Is there a way to used grouped data multiple times in a query

Suppose a table contains employee id, department name and salary. I want to select the department that gives total salary greater than average salary given by all departments. The solution that I ...
4
votes
1answer
70 views

Pull more records based on IDs in comma separated list returned by GROUP_CONCAT [duplicate]

Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, ...