sql dw count(*) with selected columns doesn’t aggregate

This is rather strange, and I have had this query work on numerous databases but here I am stumped. I know that my Synapse table has duplicates

SELECT nmiandnmisuffixkey, ReadingDate, IntervalNumber
FROM [dbo].[factMeterDataDetail]
where nmiandnmisuffixkey = 'XXXXXXXXXX'
and readingdate = '2020-10-08'
and IntervalNumber = 12

produces

+--------------------+-------------+----------------+
| nmiandnmisuffixkey | ReadingDate | IntervalNumber |
+--------------------+-------------+----------------+
| XXXXXXXXXX         | 2020-10-08  |             12 |
| XXXXXXXXXX         | 2020-10-08  |             12 |
+--------------------+-------------+----------------+

but when I try to run following

SELECT nmiandnmisuffixkey, ReadingDate, IntervalNumber, count(*) as cnt
FROM [dbo].[factMeterDataDetail]
where nmiandnmisuffixkey = 'XXXXXXXXXX'
and readingdate = '2020-10-08'
and IntervalNumber = 12
group by nmiandnmisuffixkey, ReadingDate, IntervalNumber

I get the following:-

+--------------------+-------------+----------------+-----+
| nmiandnmisuffixkey | ReadingDate | IntervalNumber | cnt |
+--------------------+-------------+----------------+-----+
| XXXXXXXXXX         | 2020-10-08  |             12 |   1 |
| XXXXXXXXXX         | 2020-10-08  |             12 |   1 |
+--------------------+-------------+----------------+-----+

why does the count not aggregate up?

Answer

Some possibilities –

date has different time/millisecond. so, you can try removing time part and run group by query again.
string column(key) can have white spaces in the end or begining. you can use ltrim/rtrim and run group by query again. Client tool will display them as identical data like your output. By trimming space there can be a true comparison.

In OP’s case, it was case #2. Using ltrim/rtrim resolved the agg issue.

Advertisement

Answer