CODEWITHSUNDEEP
sql-server
Nate Pet
Nate Pet

Reputation: 46222

Group by with further grouping

I have the following sample data:

    ID | SectionID | LocID

    1      32        12
    1      32        2
    1      32        2
    1      34        3
    1      34        4
    2      36        8
    2      36        9
    2      37        8
    2      37        9
    2      37        4

The output should be grouped by ID. The Count LocID field should show the number of DISTINCT LocIDs per sectionID totaled together.

For ID of 1, we have 2 distinct LocID for SectionID 32 and 2 for SectionID 34. Totaled equals 4

For ID of 2, we have 2 distinct LocID for SectionID 36 and 3 for sectionID 37. Total equals 5

Result: 

    ID  Count 
    1   4
    2   5

I did a group by ID but not sure how to do further grouping based on what I need. I am using SQL Server 2016.

Upvotes: 1

Views: 140

Answers (4)

user1429080
user1429080

Reputation: 9166

One more way:

select ID, COUNT(*) as SecLocCount
from (
    select distinct ID, SectionID, LocID from [MyTable]
) AS distinctRows
group by ID

Upvotes: 0

BeardOfTriumph
BeardOfTriumph

Reputation: 472

You could use a nested group by, such as

SELECT ID, SUM([Count])
FROM
(
    SELECT ID, SectionID, COUNT(DISTINCT LocID) AS [Count]
    FROM Table
    GROUP BY ID, SectionID
) Q
GROUP BY ID

Upvotes: 3

Ryan B.
Ryan B.

Reputation: 3665

The easiest way, I think, is to group by your ID and do some kind of count distinct on a concatenation of SectionID and LocID. If these are character data, you can get away with just concatenating with some kind of delimiter. If their numeric, you can do something like the example below, or convert them to strings and concat with a delimiter.

-------------------------
-- set up sample data
-------------------------

declare @datatable as table(ID int, SectionID int, LocID int)
insert into @datatable(ID, SectionID, LocID) VALUES
    (1,32,12    ),
    (1,32,2 ),
    (1,32,2 ),
    (1,34,3 ),
    (1,34,4 ),
    (2,36,8 ),
    (2,36,9 ),
    (2,37,8 ),
    (2,37,9 ),
    (2,37,4     )

-------------------------
-- The query
-------------------------

SELECT
     ID 
     ,COUNT (DISTINCT SectionID * 10000 + LocID)
FROM
    @datatable
GROUP BY ID

Gives the result:

(10 row(s) affected)
ID          
----------- -----------
1           4
2           5

(2 row(s) affected)

Upvotes: 4

Eric Brandt
Eric Brandt

Reputation: 8101

A down and dirty way is to just nest your groupings.

DECLARE @t TABLE 
(
 ID INT,
 SectionID INT,
 LocID INT 
);

INSERT INTO @t
  (
    ID
   ,SectionID
   ,LocID
  )
VALUES
  ( 1,32,12),
  ( 1,32,2),
  ( 1,32,2),
  ( 1,34,3),
  ( 1,34,4),
  ( 2,36,8),
  ( 2,36,9),
  ( 2,37,8),
  ( 2,37,9),
  ( 2,37,4) 

SELECT
  d.ID
 ,SUM(d.LocIDs) AS LocIDCnt
FROM
  (
    SELECT
      ID
     ,SectionID
     ,COUNT(DISTINCT LocID) AS LocIDs
    FROM
      @t
    GROUP BY
      ID
     ,SectionID
  ) AS d
GROUP BY
  d.ID;

Result set:

+----+-------+
| ID | Count |
+----+-------+
|  1 |     4 |
|  2 |     5 |
+----+-------+

Upvotes: 0

Related Questions