Getting row count total-CodePudding

Currently, I've got three columns of lot data. Each lot has common value set with different date stamps. I'm trying to get a count of the groupings per the common value set. example below: Table name = Data1

 Position     Lot       Date                      
    1         ABCD       2021-10-01 15:00.000
    1         ABCD       2021-10-01 15:30.000
    1         ABCD       2021-10-01 15:45.000
    2         ABCDE      2021-10-01 19:00.000
    2         ABCDE      2021-10-01 19:56.000
    2         ABCDE      2021-10-01 20:00.000

Output expected would be:
 Position    Count    Lot
    1          3     ABCD
    2          3     ABCDE

Select DISTINCT COUNT(POSITION) AS COUNT, LOT
FROM DATA1
GROUP BY POSITION;

I get a 2 count instead of 3.

CodePudding user response：

DISTINCT applies to the whole resultset and is usually applied incorrectly. Based on the 6 rows of sample data, my first guess is:

SELECT Position, Lot, [Count] = COUNT(*) 
  FROM dbo.Data1
  GROUP BY Position, Lot;

CodePudding user response：

Check this.

SELECT Position, COUNT(Position) AS Count, Lot
FROM  DATA1
GROUP BY Position, Lot