Solved

SQL - Nesting two functions (SELECT DISTINCT and SUM)

Posted on 2010-09-01
3
564 Views
Last Modified: 2012-06-21
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
Comment
Question by:Shino_skay
3 Comments
 
LVL 12

Accepted Solution

by:
mcv22 earned 250 total points
ID: 33580519
SELECT
    Product,
      SUM(Volume) AS [Total Sales]
FROM
      tblSurvey
WHERE
      Product = 'Spots'
GROUP BY
      Product
0
 
LVL 11

Assisted Solution

by:Missus Miss_Sellaneus
Missus Miss_Sellaneus earned 250 total points
ID: 33580560
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
 

Author Comment

by:Shino_skay
ID: 33580832
Silly me, that's for pointing out the GROUP BY clause which eliminates the distinct. Thanks you two.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Update data using formula 22 25
SQL Query including math and 2 tables problem 26 49
T-SQL Query to include null values 3 29
SYbase 4 24
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now