Greeting coders. I am stumped on a SQL statement that is driving me nuts. I have a sales table that I am trying to analyze for a client. The structure is as follows.
TRANSNO (unique identifier)
TRANSNO has the following construction:
7 characters total
1st character is the store (a letter)
2nd character is the register # (a number)
remaining 5 are the transaction #
distinct first characters (stores) are M,T,P,S,K,G,F
here's what i'm wanting to do:
i want the sum of qty sold for each store to be returned in 1 record
SELECT DISTINCT LEFT(transno, 1) AS Expr1, SUM(quant) AS storetot, SUM(quant * price) AS store_$
GROUP BY LEFT(transno, 1)
is VERY close but it returns each store's TOTAL # PIECES SOLD, AND TOTAL DOLLARS SOLD information on a separate record. I want them all in one record.
This is a quick solution for an experience SQL programmer and your help is greatly appreciated. I need the sql statement asap ON 9/24/03.
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.
3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).
SQL SERVER 2000