Click here to Skip to main content
12,077,574 members (49,119 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: TSQL
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 3:29am
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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

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.

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160212.1 | Last Updated 16 Oct 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100