[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

SQL - Nesting two functions (SELECT DISTINCT and SUM)

Hi,

I have 2 SQL statements I want to nest yet I can't seem to be able to find the right combination

I want the sum of all the products called "Spots" from tblSurvey. I have the sql to give me the sum.

SELECT SUM(A.Volume) AS ["Total Sales"]
FROM tblSurvey AS A
WHERE A.Product='Spots';

I have another statement that gives me the product using the SELECT DISTINCT

SELECT DISTINCT B.Product
FROM tblSurvey AS B
WHERE (((B.Product)='Spots'));

I thought I could combine the two with something like

SELECT SUM(A.Volume) AS "Total Sales"
FROM tblSurvey AS A
WHERE (SELECT DISTINCT B.[Product]
              FROM tblSurvey as B
              WHERE B.[Product] = 'Spots');

Except this sums all the numbers in the table (not using the WHERE criteria).

I tried using this method

SELECT  DISTINCT A.[Product], Sum(A.Volume) as "Total Sales",
FROM (SELECT B.[Product], B.[Volume]
              FROM tblSurvey as B
              WHERE B.[Product] = 'Spots') AS A;

But that produced an error. I'm pretty sure SELECT DISTINCT and the SUM Function can't be on the same statement, does anyone know how to achieve the desired result?

Product Total Sales
Spots    some #

Thanks


0
Shino_skay
Asked:
Shino_skay
2 Solutions
 
mcv22Commented:
SELECT
    Product,
      SUM(Volume) AS [Total Sales]
FROM
      tblSurvey
WHERE
      Product = 'Spots'
GROUP BY
      Product
0
 
Missus Miss_SellaneusCommented:
I'm not entirely sure what you're trying to accomplish. Is this what you needed?

SELECT product, SUM(volume) AS "Total Sales" FROM tblSurvey WHERE product='Spots' GROUP BY product ORDER BY product;
0
 
Shino_skayAuthor Commented:
Silly me, that's for pointing out the GROUP BY clause which eliminates the distinct. Thanks you two.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now