Group specific number of rows together and generate a group id

how to group similar items in excel
excel group rows with same value
excel group columns with header
excel group by multiple columns
how to group rows in excel with expand/collapse
group by sql
excel auto outline
excel cannot create an outline

I have a table A with 2 columns - Roll and Subject. It contains these rows:

Roll Subject
 1   Robots
 2   Robots
 3   Robots
 4   Robots
 5   Robots
 6   Space
 7   Space
 8   Space
 9   Space
10   Neurons
11   Neurons

I need to write a query and produce output as follows:

Roll Subject GroupId
   1 Robots  1
   2 Robots  1
   3 Robots  1
   4 Robots  2
   5 Robots  2
   6 Space   3
   7 Space   3
   8 Space   3
   9 Space   4
  10 Neurons 5
  11 Neurons 5

Try the following.

The -1 before the ROW_NUMBER is used so that the ROW_NUMBER values 1, 2 and 3 (which will be 0, 1 and 2) are grouped together when divided by 3, since the result is 0 (INT division).

The minimum roll by subject is to split the DENSE_RANK so it gets a new GroupID number when the subject changes.

;WITH SampleData AS
(
    SELECT
        A.Roll,
        A.Subject,
        RowNumberBySubject = -1 + ROW_NUMBER() OVER (PARTITION BY A.Subject ORDER BY A.Roll ASC),
        MinRollBySubject = MIN(A.Roll) OVER (PARTITION BY A.Subject)
    FROM
        (VALUES
            (1,'Robots'),
            (2,'Robots'),
            (3,'Robots'),
            (4,'Robots'),
            (5,'Robots'),
            (6,'Space'),
            (7,'Space'),
            (8,'Space'),
            (9,'Space'),
            (10,'Neurons'),
            (11,'Neurons') 
            ) A(Roll, [Subject])
)
SELECT
    S.Roll,
    S.Subject,
    S.RowNumberBySubject,
    S.MinRollBySubject,
    GroupIDBySubject = S.RowNumberBySubject / 3,
    GroupId = DENSE_RANK() OVER (
        ORDER BY 
            S.MinRollBySubject, 
            S.RowNumberBySubject / 3) -- GroupIDBySubject
FROM
    SampleData AS S
ORDER BY
    S.Roll ASC

Result:

Roll    Subject RowNumberBySubject  MinRollBySubject    GroupIDBySubject    GroupId
1       Robots  0                   1                   0                   1
2       Robots  1                   1                   0                   1
3       Robots  2                   1                   0                   1
4       Robots  3                   1                   1                   2
5       Robots  4                   1                   1                   2
6       Space   0                   6                   0                   3
7       Space   1                   6                   0                   3
8       Space   2                   6                   0                   3
9       Space   3                   6                   1                   4
10      Neurons 0                   10                  0                   5
11      Neurons 1                   10                  0                   5

Excel 2013: Groups and Subtotals, In Excel groups and subtotals let you can hide data and summarize groups. The selected rows or columns will be grouped. In our example, columns A, B, and C are grouped together. The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help  Pro tip: group columns. In our example, we’ve grouped rows, but you can also group columns. Select the columns you want to group and hit the Group button. It works exactly the same as grouping rows. This is useful when you have data that goes left-to-right instead of top-to-bottom. That’s common with data that’s spread over a range of time.

No just simple grouping but grouping max limit is 3

I don't understand the logic here, but according to your comment I think you are looking for

SELECT *, 
      ((ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1) / 3) + 1 GroupId
FROM T

Returns:

+------+---------+---------+
| Roll | Subject | GroupId |
+------+---------+---------+
|    1 | Robots  |       1 |
|    2 | Robots  |       1 |
|    3 | Robots  |       1 |
|    4 | Robots  |       2 |
|    5 | Robots  |       2 |
|    6 | Space   |       2 |
|    7 | Space   |       3 |
|    8 | Space   |       3 |
|    9 | Space   |       3 |
|   10 | Neurons |       4 |
|   11 | Neurons |       4 |
+------+---------+---------+

Demo

SQL GROUP BY | COUNT, The GROUP BY clause groups records into summary rows. GROUP BY SELECT COUNT(Id), Country; FROM Customer; GROUP BY Country. Results: 21​  My table contains the details like with two fields: ID DisplayName 1 Editor 1 Reviewer 7 EIC 7 Editor 7 Reviewer 7 Editor 19 EIC 19 Edito

This is what you expect

SELECT *,NTILE(3) OVER(ORDER BY [Roll])AS GroupID FROM 
(
VALUES
(1,'Robots'),
(2,'Robots'),
(3,'Robots'),
(4,'Robots'),
(5,'Robots'),
(6,'Space'),
(7,'Space'),
(8,'Space'),
(9,'Space'),
(10,'Neurons'),
(11,'Neurons') 
)A (Roll,[Subject])

Beginning Visual Basic 2005 Databases, The data type for the GroupID column in Oracle is defined as a CHAR(36) data Remember that you do not specify the number of characters in a data type in your CREATE OR REPLACE PROCEDURE usp_DeleteGroup ( inGroupID CHAR ) will select a specific row of data from the Groups table based on the Group ID  ;WITH sumCTE AS ( SELECT Rel.t2ID, SUM(Price) price FROM @t1 AS T1 JOIN @relation AS Rel ON Rel.t1ID=T1.ID GROUP BY Rel.t2ID ) ,maxCTE AS ( SELECT Rel.t2ID, Rel.t1ID, ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList FROM @t1 AS T1 JOIN @relation AS Rel ON Rel.t1ID=T1.ID ) SELECT T2.ID AS T2ID ,T2.Name as T2Name ,T2.Orders

Group rows in a table (Power Query) - Excel, In the Query Editor, you can group the values in various rows into a single value Power Query has two types of Group By operations: aggregate a column with an right-clicked in step 1 is (by default) selected in the Group By drop-down list. Count Rows for grouping, the new grouped column only displays the number of  I want to (a) group all cases by social security number, (b) assign those cases a unique ID and then (c) remove the social security number. Here's an example data set: personal_id gender temperature 111-11-1111 M 99.6 999-999-999 F 98.2 111-11-1111 M 97.8 999-999-999 F 98.3 888-88-8888 F 99.0 111-11-1111 M 98.9

Group By, Having & Count, For instance, if we SELECT the COUNT() of the ID column in the pets table, it will We can use GROUP BY to group together rows that have the same value in the from google.cloud import bigquery # Create a "Client" object client = bigquery. the number of comments that were made as responses to a specific comment. When _n is combined with by, however, _n is the observation number within by-group, in this case, within oldid. If there were three oldid ==1 observations followed by two oldid ==2 observations in the dataset, _n would take on the values 1, 2, 3, 1, 2.

How to Group Rows and Cells in Excel, Grouping and outlining data (like rows and cells) make your Why would you create an outline for nested data? This tells Excel which pieces of data belong together, and which levels You can also minimize all the groups at a specific level by clicking one of the numbers at the VAT-ID: DK-40428631. GROUPING_ID (Transact-SQL) 03/03/2017; 9 minutes to read; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse . Is a function that computes the level of grouping. GROUPING_ID can be used only in the SELECT <select> list, HAVING, or ORDER BY clauses when GROUP BY is specified.

Comments
  • Provide Minimal, Complete, and Verifiable example
  • Any logic except the expected output?
  • No just simple grouping but grouping max limit is 3
  • Sounds a lot like homework. What have you tried? What are you getting with your results?
  • Looks like a simple loop. So I suggest not to do this in SQL, but with a programming language instead.
  • Thank you Sir, for helping me out. Your solution perfectly satisfy all my test cases and scenarios.
  • @HRISHIKESHBAGCHI Glad to help. You can mark the question as answered so it isn't left open. Regards.
  • First Space must be in different group (3), if I understand question correctly.
  • Close, need to add PARTITION BY Subject before the ORDER BY. NTILE will also reset the grouping number for each new group.