Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [group-by]

GROUP BY : SQL Aggregation for Common Values

According to Wikipedia.com

The GROUP BY clause is used to project rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.

Depending on the Dialect of SQL, modifiers and functions can be applied to GROUP BY.

Functions can include: MAX(), MIN(), COUNT(), AVG(), SUM(), etc.

Other Definitions Provided by

741 questions
4
votes
4 answers

GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

TL;DR - I would like examples of where using a GROUP BY (example shown below) using columns not in the SELECT list can be used to resolve SQL challenges in a [practical | elegant | powerful] way. I mean in a general way - the example demonstrated…
Vérace
  • 29,825
  • 9
  • 70
  • 84
2
votes
1 answer

Same Variable Changing its Min/Max Values within the Same Group?

I am working with Netezza SQL. I have the following table ("my_table"): gender country favorite_color disease height weight id 1 m mexico blue n 193.6547 62.74102 1 2 m canada red n 159.4800…
stats_noob
  • 143
  • 7
2
votes
2 answers

Group and order output into a single row

I want to display each person's contact info and their commissions in a single row as opposed to multiple rows. Commission Table ID COMMISSION DATE N12545233 774 4/1/15 …
AlGator
  • 333
  • 1
  • 2
  • 5
1
vote
1 answer

GROUP BY with OR/AND clause?

Lets say my database looks like this: Table: messages +----+------+----+----------+----------+ | id | from | to | double_1 | double_2 | +----+------+----+----------+----------+ | 1 | 1 | 2 | 0 | 0 | | 2 | 1 | 0 …
Mark Topper
  • 13
  • 1
  • 5
1
vote
0 answers

Calculating Percentages within Percentiles

I am working with Netezza SQL. I have the following table: CREATE TABLE MY_TABLE ( id INT, gender VARCHAR(1), disease VARCHAR(1), income DECIMAL(10, 3) ); INSERT INTO MY_TABLE (id, gender, disease, income) VALUES ('1', 'f', 'y',…
stats_noob
  • 143
  • 7
0
votes
0 answers

Modifying an SQL query for All Combinations of Two Variables

I am working with Netezza SQL. I have the following table ("my_table"): id year var1 var3 date_1 1 1 2017 1 1 NA 2 1 2018 0 1 NA 3 1 2019 1 1 NA 4 2 2017 0 1 NA 5 2 2018 1 1 NA 6 3 2017 1 1 …
stats_noob
  • 143
  • 7
0
votes
0 answers

Finding Out All Possible Combinations of Years Per ID per Group

I am working with Netezza SQL. I have the following table ("my_table") which has information on university students over a period of years (2010-2015), the major they were enrolled in during a given year, the results of an exam (1 = pass, 0 = fail)…
stats_noob
  • 143
  • 7
0
votes
0 answers

creating max_variables within subqueries

I have SQL query: select a.merge_key, a.var1, a.var2, a.var3, a.name_of_person, a.name_of_automobile, a.price_of_automobile, b.merge_key as m, b.var1 as v, b.var5, b.var6, b.var7, from first_table a inner join second_table b on a.merge_key =…
stats_noob
  • 143
  • 7
0
votes
1 answer

Having and Group By clause

I came across this question yesterday. I am looking for an explanation for just one point. Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q : An…
Sudhir Sharma
  • 113
  • 4
0
votes
1 answer

SQL group by multiple columns

Say I have the following table, where a group of 3 people from 3 locations work together on a project that is scored. | | Score | Possible_Score | Loc_A | Loc_B | Loc_C | |---|-------|----------------|-------|-------|-------| | 1 | 80 | 100 …
lynak
  • 1
0
votes
1 answer

Fetch column data against group by data

The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image. Now I need balance of the row fetched using group by. Please help.
user80133
  • 1