0

I'm getting an error from an sqlite3 query for which I can't find the reason.

CREATE TABLE professors (
    firstname            text,
    lastname             text,
    university_shortname text
);

Query:

SELECT COUNT(DISTINCT(firstname, lastname)) FROM professors;

Error:

OperationalError: row value misused

user4157124
  • 2,809
  • 13
  • 27
  • 42
Vanessa
  • 89
  • 6

1 Answers1

1

COUNT() aggregate function takes only 1 argument and that argument can't be a row value like (firstname, lastname).

A workaround for your requirement would be the use of DENSE_RANK() window function:

SELECT DENSE_RANK() OVER (ORDER BY firstname, lastname) AS distinct_count
FROM professors
ORDER BY distinct_count DESC LIMIT 1;

See a simplified demo.

Note that the above query will count all the distinct combinations of firstname and lastname, even the ones where one or both columns are null.

forpas
  • 160,666
  • 10
  • 38
  • 76