CODEWITHSUNDEEP
sqlsql-servert-sql
Diego83
Diego83

Reputation: 73

Getting row count total

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.

Upvotes: 0

Views: 51

Answers (2)

Rashedul Alam
Rashedul Alam

Reputation: 156

Check this.

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

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

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;

Upvotes: 2

Related Questions