Trying to get sum of size of unique filenames.
I have a table with filename and size. filename is not unique, however if the filenames are the same they are the same file, so I only want to count their size once.
So I want the total file size of all unique filenames, I do not want the sum of distinct sizes, because some different files can have the same size.
SELECT DISTINCT Filename, SUM([Size]) --fails
SELECT COUNT(DISTINCT Filename), SUM([Size]) --returns correct filename count, but the total sum, not distinct sum
SELECT SUM(DISTINCT [Size]) --returns sum of distinct sizes, not sum of distinct filenames.
I sure I could do it with a more elaborate, compound query, but I'm trying to be efficient as this will be called often from a dashboard.
Any guidance appreciated!