Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL

Posted on 2006-03-20
4
Medium Priority
?
330 Views
Last Modified: 2012-06-21
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...
0
Comment
Question by:wilflife
4 Comments
 
LVL 7

Expert Comment

by:jaanth
ID: 16237197
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
 

Author Comment

by:wilflife
ID: 16237281
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 750 total points
ID: 16238098
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 750 total points
ID: 16238913
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question