fine tuning results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

fine tuning results

Author

Topic

lambo99
Starting Member

4 Posts

Posted - 2008-03-02 : 17:17:50

Hi,

I am running the query below but I am getting the following results:-

Store Prod_no Quant_Sold Total Orders Total_Sale
115 M1015 4 4 4000
115 M1015 2 1 2000

For some reason on some of the products there is more than one entry for that day per store. As you can see above there is two entries for the product M1015. I need the results to look like the below:

Store Prod_no Quant_Sold Total Orders Total_Sale
115 M1015 6 5 6000

SELECT

store.branch_no AS store_no,

crea_date,

jobstock.prod_no,

((jobstock.quantity)*COUNT(jobstock.job_id)) AS quantity_sold,

COUNT(jobstock.job_id) AS total_orders,

SUM(jobstock.total*100) AS store_total,

cast (SUM(jobstock.total/1.25)*100 AS INTEGER) AS store_total_before_tax,

((((stock.curr_cost*jobstock.quantity)*100)*COUNT(jobstock.job_id))) AS cost

FROM store, pickup, jobstock, stock
WHERE (store.store_id=pickup.store_id)
AND (pickup.job_id=jobstock.job_id)
AND (stock.prod_no=jobstock.prod_no)
AND (stock.store_id=pickup.store_id)
AND branch_no>0
and crea_date between '2008-02-25' and '2008-02-28'
GROUP BY store.branch_no, jobstock.prod_no, jobstock.quantity, stock.curr_cost, crea_date
ORDER BY crea_date

Any Ideas would be much appreciated.

thanks you.

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2008-03-02 : 18:01:09

Do not group by jobstock.quantity, stock.curr_cost. Remove thos columns form the Grop by clause

---------------------
http://dineshasanka.spaces.live.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources