CODEWITHSUNDEEP
mysqlsql
Josh Bond
Josh Bond

Reputation: 1819

SQL - Count unique values of a column produced from a query

I'm querying a table that records file downloads from a popular community website software. The SQL below shows the first file each user downloaded.

SELECT
    dfid, dtime, file_name, MIN(dmid)
FROM
    downloads_downloads
JOIN downloads_files
ON downloads_downloads.dfid = downloads_files.file_id
GROUP BY
    dmid
ORDER BY
dtime,dfid

The data from this query has thousands of rows like:

dfid    dtime       file_name   MIN(dmid)
3       1308147507  abc             108
455     1308149115  def             109
788     1308156732  ghi             113
7       1308156830  xyz             112
455     1308158073  def             114

Can I count each unique instance of file_name to show a the number of times each file_name value appears? Doesn't matter about the exact layout. I'd like to do this without creating a table and inserting the data into it, if possible. For example, in the above data, the file_name "def" appears twice, so it would be counted twice.

Upvotes: 3

Views: 1607

Answers (1)

Steve Bergamini
Steve Bergamini

Reputation: 14600

You'd just need to do something like:

SELECT file_name, COUNT(*) FROM downloads_downloads GROUP BY file_name;

Upvotes: 5

Related Questions