S S abbasi S S abbasi - 7 months ago 10
SQL Question

Using two tables to get one report in SQL Server

I have two tables,

product
and
download
as follows.


  • product
    (product_id (pk), product_name)

  • download
    (download_date(pk), download_version(pk), product_id(pk,fk))



I need a report to show how many downloaded, form which version of what product took place in each month.

SELECT
[product_name],
[version],
MONTH(download_date) AS [Month],
COUNT(MONTH(download_date)) AS [Count]
FROM
product
INNER JOIN
download ON product.product_id = download.product_id
GROUP BY
MONTH(download_date)


and I get this error


Column 'product.product_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer
  1. Use alias names for the tables for better readability.
  2. Mention the aliasname.columnname in the SELECT to avoid fetching the wrong values.
  3. You missed the other columns except the aggregate values in the GROUP BY.

So the query below will return the result.

SELECT P.[product_name],
      P.[version],
      MONTH(D.download_date) AS [Month],
      COUNT(MONTH(D.download_date)) AS [Count]  
FROM product P
INNER JOIN download D ON D.product_id = P.product_id 
GROUP BY P.[product_name], P.[version], MONTH(D.download_date)