Useful Queries (Group By example)

Find out the results on the basis of grouping

Sometimes we got the requirement where we have to group the results on the basis of a column. Let me explain you steps by step:-

Step 1: Create a table first


CREATE TABLE groupingExample(
id INT IDENTITY (1, 1)
,exc_idn INT
,lot_nbr VARCHAR(20)
)
GO

Step 2: Insert the sample record into this table


INSERT INTO groupingExample (exc_idn, lot_nbr)
SELECT 5760441, 'F0389441'
UNION ALL
SELECT 5760441, 'F0389541'
UNION ALL
SELECT 5760517, 'F0367191'
UNION ALL
SELECT 5760517, 'F0384651'
UNION ALL
SELECT 5760523, 'F0367191'
UNION ALL
SELECT 5760523, 'F0384651'
UNION ALL
SELECT 5760643, 'F0366961'
UNION ALL
SELECT 5760885, 'F0365977'
UNION ALL
SELECT 5764109, 'F0312698'
UNION ALL
SELECT 5764109, 'F0312671'
GO

Step 3: Now write a query which will give that each exc_idn contain how many lot_nbr in this table. Let's do it in this way

SELECT exc_idn, COUNT(lot_nbr) COUN FROM groupingExample GROUP BY exc_idn

And the output would be something like

exc_idn         COUN      

5760441         2
5760517         2
5760523         2
5760643         1
5760885         1
5764109         2

You can also use the order by clause with the above query like

SELECT exc_idn, COUNT(lot_nbr) COUNT FROM groupingExample GROUP BY exc_idn order by 2 desc


Thanks.