SQL

Hi,

I have a table with about 10 columns

Product
Country
Date     In text format JAN-09
Week Number
Product SubCategory
Product Description
Unit measure.
Units Sold
Vol Sold

I would like to do two things...

Aggregate up all the sales by summing units sold for each month
And also sum up the volume sold.
This should be returned by the same query.

All the other columns should be brought across with no calcs ie the units and this suming should be done at the month level so i have to split the month off from the string as well.

Thanks for help...
wilflifeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jaanthCommented:
What you are looking for is the sum function, which requires a group by clause on a select statement.
Since you want months, you will need to drop the week field from your query.

select
Product,
Country,
Product SubCategory,
Product Description,
Unit measure.
Date,                   --     In text format JAN-09
UnitsSold = sum(Units Sold),
VolSold = sum(Vol Sold)
from tablename1
group by
Product,
Country,
Product SubCategory,
Product Description,
Unit measure.
Date

Hope this helps.

0
wilflifeAuthor Commented:
Will this give me it all grouped by the month.... as i know when i use the sum i have to group by everything but effectively i only want it grouped by the month and all other columns simply transported across... will this simple sql do it?

Thanks.
0
Aneesh RetnakaranDatabase AdministratorCommented:
i am not sure about tour questuion, Pls run the following queries one by one

select
    Product,
    Country,
    [Product SubCategory],
    [Product Description],
    [Unit measure],
    LEFT(Date,3) AS [Month],
    UnitsSold = sum([Units Sold]),
    VolSold = sum([Vol Sold])
from tablename1
GROUP BY Product,Country,[Product SubCategory],[Product Description],
[Unit measure],[Month]
WITH ROLLUP


OR



select
    Product,
    Country,
    [Product SubCategory],
    [Product Description],
    [Unit measure],
    LEFT(Date,3) AS [Month],
    UnitsSold = sum([Units Sold]),
    VolSold = sum([Vol Sold])
from tablename1
GROUP BY Product,Country,[Product SubCategory],[Product Description],
[Unit measure],[Month]
WITH CUBE
0
Brian CroweDatabase AdministratorCommented:
SELECT myTable.product, myTable.Country, ...
   (SELECT SUM(B.[Units Sold]) FROM myTable AS B WHERE B.Date = myTable.Date) AS MonthlyUnitsSold,
   (SELECT SUM(C.[Vol Sold]) FROM myTable AS C WHERE C.Date = myTable.Date) AS MonthlyVolSold
FROM myTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.