0

I have three tables, I want to count the tbl_repository with how many rows are stored there. tbl_downloadcount and tbl_recentlyview I want to sum all the numeric values in the cilumn which is download_count and number_views.

SELECT SUM(tbl_downloadcount.download_count) AS dlcount, SUM(tbl_recentlyview.number_views) AS numviews, COUNT(DISTINCT tbl_repository.document_added) AS publish FROM tbl_downloadcount, tbl_recentlyview, tbl_repository

my download_count SHOULD have a total of 6 (4+1+1) the number_views SHOULD have a total of 7 (3+2+2) ...

Whenever I run the query it result me a dlcount = 54 / numviews = 63 / publish = 3 and publish is the only the right result(which is 3)

europa
  • 1
  • Is there a particular reason why you wouldn't want to run this as three separate, simple and straightforward queries? – El_Vanja May 10 '21 at 12:12
  • @El_Vanja Oh I know how to do this in separated queries however I was looking for if there are possible single query for this one. – europa May 10 '21 at 12:13
  • Yeah, but my question was why. It just adds complexity to the query and, as such, to your code. As for the solution, one of them would be a `UNION` of the three separate queries. – El_Vanja May 10 '21 at 12:16
  • @El_Vanja Oh I see, I tried your suggested solution which is `UNION` and it worked, however it only shows one alias. Is this where the complexity starts? Anw thank you. – europa May 10 '21 at 12:21
  • Yeah, that's the drawback of the union, you can only unify sets with the same columns (it treats all three results as the having the same column, so you get your results as rows instead of columns). There might be other solutions, but my SQL-Fu is not that strong. Either way, nothing wrong with separate queries. – El_Vanja May 10 '21 at 12:25
  • Also, [this](https://stackoverflow.com/questions/3761240/count-from-multiple-tables-in-mysql) question might be relevant. – El_Vanja May 10 '21 at 12:29
  • @El_Vanja I just tried this one and it worked, how do I mark your recently reply as an answer? – europa May 10 '21 at 12:36
  • You can't, because it's only a comment. If an existing answer is applicable to your question, then the site policy is to close your question as a duplicate of the existing one, because content duplication is something Stack Overflow wishes to avoid (no point in repeating the same things over and over). – El_Vanja May 10 '21 at 12:45
  • I've flagged your question as a duplicate. I believe that as the asker you can accept that as the duplicate, or you can remove your question entirely. Up to you. Glad I could help and good luck in further development. – El_Vanja May 10 '21 at 12:47

0 Answers0