MySQL - COUNT() with non-aggregate columns

Question

Related question: MySQL "Group By" and "Order By"

I can use the following query to retrieve a list of downloads for an item:

SELECT items.id, items.name, users.fullName AS downloadedBy, dl.dateTime AS lastDownload
FROM items
    LEFT OUTER JOIN downloads dl ON dl.itemID = items.id
    LEFT JOIN users ON users.id = dl.userID
WHERE items.id = '47'
ORDER BY dl.dateTime DESC

This, as expected, returns a list of downloads with the latest at the top.

What I want to do is retrieve the last download and the total number of downloads for the item.

This query:

SELECT items.id, items.name, users.fullName AS downloadedBy, dl.dateTime AS lastDownload, COUNT(dl.id) AS total
FROM items
    LEFT OUTER JOIN downloads dl ON dl.itemID = items.id
    LEFT JOIN users ON users.id = dl.userID
WHERE items.id = '47'
ORDER BY dl.dateTime DESC
GROUP BY items.id

returns the correct total, but the download details (user name and date) is a random entry from the middle of the list. Wrapping the main query in a subquery and putting the GROUP BY afterwards, as described in the linked question, has the same effect. Is it possible to retrieve both the last download and the total in one query?

I'm using MySQL version 5.7.21 with ONLY_FULL_GROUP_BY disabled.

[EDIT] To answer Ankit's comment:

Output from first query:

id | name    | downloadedBy | lastDownload 
==============================================
47 | Item 47 | Matilda      | 2018-08-18 19:10
47 | Item 47 | Marmaduke    | 2018-08-14 07:21
47 | Item 47 | Simon        | 2018-06-11 14:02
47 | Item 47 | Boris        | 2018-06-11 14:00

Desired output:

id | name    | downloadedBy | lastDownload     | total
======================================================
47 | Item 47 | Matilda      | 2018-08-18 19:10 | 4

Actual output from second query:

id | name    | downloadedBy | lastDownload     | total
======================================================
47 | Item 47 | Simon        | 2018-06-11 14:02 | 4

Please post the sample data and expected output. – Ankit Bajpai Aug 18 '18 at 18:23 — Ankit Bajpai, Aug 18 '18 at 18:23

score 0 · Accepted Answer · answered Aug 18 '18 at 21:29

What you are trying to query is two separate things that have separate logic. The last download queries one row whereas the count of rows calculates number of rows and has nothing to do with the last download query.

The reason why your query does not work, is that the aggregate function is calculated per each normal column (id, name, fullName and dateTime). You would get count for each of those combinations if you did not disable the ONLY_FULL_GROUP_BY. Now that you did, MySQL will pick random value from the result set for those. Disabling ONLY_FULL_GROUP_BY is generally a bad thing. It protects you from wrong usage of GROUP BY.

To make the query you can do separate queries for the two things you are interested in and then combine them as one.

select max(id), max(name), max(downloadedBy), max(lastDownload), max(cnt)
from (
  (SELECT items.id, items.name, users.fullName AS downloadedBy, dl.dateTime AS lastDownload
  FROM items
      LEFT OUTER JOIN downloads dl ON dl.itemID = items.id
      LEFT JOIN users ON users.id = dl.userID
  WHERE items.id = '47'
  ORDER BY dl.dateTime DESC
  limit 1)
  union
  (select null, null, null, null, count(*)
  FROM items
      LEFT OUTER JOIN downloads dl ON dl.itemID = items.id
      LEFT JOIN users ON users.id = dl.userID
  WHERE items.id = '47')
) q;

Thanks for the answer - I think I'll just use mysqli_num_rows to get the total, if this is the case. {EDIT} - Actually, that won't work for zero downloads. But it still looks like it's easier to do it in the PHP rather than in the SQL — Tevildo, Aug 18 '18 at 21:37

MySQL - COUNT() with non-aggregate columns

1 Answers1