Need a specific SQL Select Stament!

Question

0.00/5 (No votes)

See more:

Hi! This might seem stupid but I am looking for a simple select statement for my database. Scenario:

I have a database that contains: Province, CustomerName and a SalesValue. I want to ouput ALL of the CustomerName with their SalesValue and the Province and then, the tricky part for me: view the total sales for each Province the customer has made in each Province.

I feel stupid and this is the firs time I struggled with such a statement.
I can write a stored procedure with the SUM function but it needs to output all of the records. Which means I cant give it a specific WHERE clause on a specific CustomerName!

ex. Fields: CustomerName Province SalesValue TotalSalesPerProvince

Output: Rachel Gauteng 500 1500

HELP!!!

Posted 16-Oct-12 2:29am

Member 7969484

Add a Solution

Comments

Zoltán Zörgő 16-Oct-12 8:36am

This makes no sense: you either select SalesValue or the sum of SalesValue based on some grouping. Or where is TotalSalesPerProvince coming from? Plus: you speek about database, but I see it as a table. Please provide the schema of your table or tables (with their relations) and a more complex sample for input tables and the query result you want.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-10-16T02:43:00

Solution 1

Try looking at the GROUP BY clause: http://www.w3schools.com/sql/sql_groupby.asp[^]

It does what you want, but I can't give you an example without knowing more about your database and data.

Posted 16-Oct-12 2:43am

OriginalGriff

Afzal Shaikh · Accepted Answer · 2012-10-16T02:45:00

1. I want to ouput ALL of the CustomerName with their SalesValue and the Province

SQL

Select CustomerName,SalesValue,Province into #Temp
from tbl_Name

2. and then, the tricky part for me: view the total sales for each Province the customer has made in each Province.

SQL

Select CustomerName,Province ,Sum(SalesValue)from tbl_Name a
inner join #Temp b on a.CustomerValue=b.CustomerValue
group by CustomerName,Province