Sum and Count - 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

Sum and Count

Author

Topic

Chilly
Starting Member

3 Posts

Posted - 2008-03-05 : 17:49:08

I am trying to create a single query to provide a distinct part count, to calculate quantity in stock * current price and break it out by site. I am using the following code then export to excel and calculate. This was OK the first time or 2 and now it is becomming required information.

SELECT A2.SEGREGATION_CODE,A2.PART,A1.SITE_CODE,A2.QUANTITY,A2.CURRENT_PRICE
FROM SC01 A1, ITEM A2
WHERE A1.SEGREGATION_CODE = A2.SEGREGATION_CODE AND A2.STATUS_AVAIL = 'Y' AND A1.SITE_CODE = 'TAMPA'

I have used the below to retrieve a count of distince parts
SELECT COUNT (DISTINCT PART)
FROM ITEM
WHERE STATUS_AVAIL = 'Y'

I also used the below to retrieve a total dollar amount
SELECT SUM (QUANTITY * CURRENT_PRICE)
FROM ITEM
WHERE STATUS_AVAIL = 'Y'

Now trying to mix the 2 and breakout by site is driving me crazy. Any help is greatly appreciated.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 17:56:41

you are doing these and exporting to excel to calculation the multiplication?


SELECT COUNT(Distinct A2.PART) as Units, 
       SUM(A2.Quantity*A2.Current_Current_price) as DollarInventory ,
       A2.SEGREGATION_CODE,
       A2.PART,A1.SITE_CODE,
       A2.QUANTITY,A2.CURRENT_PRICE
FROM SC01 A1 INNER JOIN ITEM A2 on A1.Segregation_Code = A2.Segregation_Code 
             and A2.Status_Avail = 'Y'
             AND A1.Site_Code = 'Tampa'
Group By  A2.SEGREGATION_CODE,
          A2.PART,A1.SITE_CODE,
          A2.QUANTITY,A2.CURRENT_PRICE

won't something like that work?

Poor planning on your part does not constitute an emergency on my part.

Chilly
Starting Member

3 Posts

Posted - 2008-07-16 : 16:12:49

The INNER JOIN did the trick. Sorry for the lapse in a response.

Thankx for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources