Query for SUM

Hi, i have a simple table with itemcode and quantity

Code Qty

--

A 2

B 3

C 5

A 3

How do i query the table to return me the total quantity by itemcode (as below)?

Code Qty

--

A 5

B 3

C 5

use aggregate function sum () and grup by

Select code,sum(qty) as TotalQty from YourTableName group by Code

Madhu

|||

use the following code,

Code Snippet

Create Table #data (

[Code] Varchar(100) ,

[Qty] int

);

Insert Into #data Values('A','2');

Insert Into #data Values('B','3');

Insert Into #data Values('C','5');

Insert Into #data Values('A','3');

Select

Code

,Sum(Qty)

From

#Data

Group By

Code

Query for percentage of a SUM

I am trying to figure out the syntax for a query that will essentially
give me the Percentage each of my areas contributes to the Whole. I
know this can be achieved by multiple queries but I would like to keep
it intact as one single query if possible.

For Example I have the following data set--

AREA MOU
NE 1234
SO 4312
WE 12312
MW 97123
NE 1123
SO 31
WE 312
MW 971

The results I would like to see would look like

AREA MOU PERCENT
MW 98094 .83536
NE 2357 .02007
WE 12624 .10751
SO 4352 .03706

The query I came up with is--

SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, sum(MOU) /
(SELECT SUM(MOU) AS TOTAL_MOU
FROM [2004_NOVEMBER_COST])as
[PERCENT]
FROM [2004_NOVEMBER_COST]
GROUP BY Area

All seems to calculate with the exception of the Percent where all the
percentages are 0's.

I think I need to take the first line AREA_TOTAL and now divide by the
SUM(MOU) like this--
SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /
(SELECT SUM(MOU) AS TOTAL_MOU

but I get Invalid Column.

I essence I think it is a simple query but I am hitting a wall. Any
advice would help.

Thanks,
BenPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You might also stop putting numbers at the front of names and using
proprietary square brackets. It looks like you are using one table per
month per year!! That would certainly not be the case in a properly
designed database; that would be serious attribute splitting.

Try qualifying the names better, something like this. Without DDL,
this is a wild guess:

SELECT A1.area, SUM(A1.mou) AS area_total,
(SUM (A1.mou) / (SELECT SUM (A2.mou)
FROM AreaCosts AS A2
WHERE A2.report_month = 11 ) ) AS
percentage
FROM AreaCosts AS A1
WHERE A1.report_month = 11
GROUP BY A1.area;|||On 13 Dec 2004 17:16:39 -0800, Ben wrote:

>All seems to calculate with the exception of the Percent where all the
>percentages are 0's.

Hi Ben,

I didn't study your query in detail, but I guess that it's correct (though
you don't need the DISTINCT if you already do GROUP BY!).

The reason the percentages are 0 is because SQL Server will use integer
division (both operands of the / operator are integer), discarding the
fractional part. Try running these:
SELECT 7 / 8
SELECT 7 / 8.0
SELECT 7.0 / 8

As you see, making sure that at least one of the operands is not integer
is enough to get an exact result. To get the same effect in your query,
just change "sum(MOU) / ..." to "CAST(sum(MOU) AS numeric) / ..."

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

Ben (wiredog@.comcast.net) writes:
> All seems to calculate with the exception of the Percent where all the
> percentages are 0's.
> I think I need to take the first line AREA_TOTAL and now divide by the
> SUM(MOU) like this--
> SELECT DISTINCT Area, SUM(MOU) AS AREA_TOTAL, AREA_TOTAL /
> (SELECT SUM(MOU) AS TOTAL_MOU
> but I get Invalid Column.

You cannot use a column alias later in the query. Just imagine the
table you are querying actually have an AREA_TOTAL column. What would
happen then?

This may be the best way to write the query.

SELECT Area, SUM(MOU) AS Area_total, 1.0 * SUM(MOU) / x.grand_total
FROM tbl
CROSS JOIN (SELECT grand_total = SUM(MOU) FROM tbl) AS x
GROUP BY Area

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the top
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/fr
DROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=
(Select @.Percentage * SUM(some_amount) from #SomeTable)
|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it with
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr
sql

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the top
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/frDROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=(Select @.Percentage * SUM(some_amount) from #SomeTable)|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it with
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
--
thanks,
--
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:
> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of the
> column and then cut off at a given percentage. IE if i'd like to find the top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr

Query for finding rows whose sum constitues percentage of total su

Hi.
Given a table that looks like:
unique_ID, some_amount
1 10
2 15
3 7
4 20
5 8
6 7
...
What i want is to find the rows that contribute most to the total sum of the
column and then cut off at a given percentage. IE if i'd like to find the to
p
30% contributers to the total sum in the example above the result would be
row 4 (total being 67 and row 4 contributes with 30% of that).
Any help very much appreciated.
/frDROP Table #SomeTable
CREATE TABLE #SomeTable
(
unique_id INT Identity(1,1),
some_Amount INT
)
INSERt INTO #SomeTable(Some_Amount)
SELECT 10
INSERt INTO #SomeTable(Some_Amount)
SELECT 15
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
INSERt INTO #SomeTable(Some_Amount)
SELECT 20
INSERt INTO #SomeTable(Some_Amount)
SELECT 8
INSERt INTO #SomeTable(Some_Amount)
SELECT 7
DECLARE @.Percentage DECIMAL(20,2)
SET @.Percentage = 0.25
Select * from #SomeTable
where some_amount >=
(Select @.Percentage * SUM(some_amount) from #SomeTable)|||hi ragnar,
here's a script
I put the amount on a table with id and amount fields
hope it helps
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
you must cast the numerator in some datatype that accept the decimal place.
In my case i make it money. Other wise you get zero and then mulitply it wit
h
100 to get the percentage. the rest is up to you
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||hi,
here's an additional help
select id,amount, cast(amount as money)/(select sum(amount) from test1)*100
as percentage from test1 x
where cast(amount as money)/(select sum(amount) from test1)*100 > 25
order by 3
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr|||Try,
select 30 percent *
from t1
order by some_amount desc
-- or
select 30 percent with ties *
from t1
order by some_amount desc
AMB
"Ragnar" wrote:

> Hi.
> Given a table that looks like:
> unique_ID, some_amount
> 1 10
> 2 15
> 3 7
> 4 20
> 5 8
> 6 7
> ...
> What i want is to find the rows that contribute most to the total sum of t
he
> column and then cut off at a given percentage. IE if i'd like to find the
top
> 30% contributers to the total sum in the example above the result would be
> row 4 (total being 67 and row 4 contributes with 30% of that).
> Any help very much appreciated.
> /fr

Query Data using multiple select statement

Hello everyone!

Is this statement is possible? here is the code:

select sum(nPassengers) Planned, Actual, (sum(Planned) - Actual) Variance
from (
select iDeptCode, sum(nPassengers) Planned from tbl_BusRequest
where iReqTime = '1' and iDeptCode = '1' and cReqType = 'I'
group by iDeptCode
and
(Actual = (Select nPassengers from tbl_Riders_NonRiders where dtDate = '11/05/06'
AND cReqType = 'i' and iTimeCode = '1' and iDeptCode = '1' )

and im getting this error:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'and'.

Thankssee your previous post for the possible solution|||

Quote:

Originally Posted by intscript

Hello everyone!

Is this statement is possible? here is the code:

select sum(nPassengers) Planned, Actual, (sum(Planned) - Actual) Variance
from (
select iDeptCode, sum(nPassengers) Planned from tbl_BusRequest
where iReqTime = '1' and iDeptCode = '1' and cReqType = 'I'
group by iDeptCode
and
(Actual = (Select nPassengers from tbl_Riders_NonRiders where dtDate = '11/05/06'
AND cReqType = 'i' and iTimeCode = '1' and iDeptCode = '1' )

and im getting this error:

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'and'.

Thanks

You may want to put the results of the second 1/2 of your query in a temp table, then select from there.

A bit easier to read, if nothing else

Query by Time-Slices

Dear NG,
I want to query a table like this:
Customer | Money | Time
A 12 12:01
B 17 12:03
C 25 12:06
D 14 12:09
E 10 12:11
...
My target is to have the SUM of Money Transfers by TIME SLICE, eg.
12:00 - 12:05: 29
12:05 - 12:10: 39
12:10 - 12:15: 10
...
How can I query this?
Thank you very much
RudiI am not sure what format your "Time" column is stored, but perhaps this
will get you started:
SELECT SUM(SomeColumn) AS SomeColumn, 'TheTimeSlice' = CASE WHEN TheTime
BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05' WHEN TheTime BETWEEN
'12:06' AND '12:10' THEN '12:06 - 12:10' END
FROM YourTable
GROUP BY CASE WHEN TheTime BETWEEN '12:00' AND '12:05' THEN '12:00 - 12:05'
WHEN TheTime BETWEEN '12:06' AND '12:10' THEN '12:06 - 12:10' END
Keith Kratochvil
<rudolf.ball@.asfinag.at> wrote in message
news:1141651834.317186.293110@.i39g2000cwa.googlegroups.com...
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi
>|||Thank you very much,
but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
CASE?
Thank you
Rudi|||Please post table DDLs so that others can understand the datatypes, keys,
constraints etc. What is the datatype of the time column? Also time and
money are reserved words in t-SQL.
Your sample data is not very clear. Do you need rows for all 5 minute slice
irrespective of whether a transfer occured or not? If a transfer occured
exactly on 12:05 ( AM/PM ) , do you want to account for it in the 12:00 -
12:05 group or 12:05 - 12:10 group?
As a general response, for such problems consider integer division on the
expression that returns the number of minutes from the starting point. For
instance, in this case it would be: DATEDIFF( minute, 0, time_ ) / 5. You
can use this expression in your GROUP BY clause to return the resultset you
want.
Anith|||Yes you can, by relying on integer division.
CREATE TABLE Test(Customer char(1),Money int,Time smalldatetime)
INSERT INTO Test VALUES ('A',12,'12:01')
INSERT INTO Test VALUES ('B',17,'12:03')
INSERT INTO Test VALUES ('C',25,'12:06')
INSERT INTO Test VALUES ('D',14,'12:09')
INSERT INTO Test VALUES ('E',10,'12:11')
SELECT CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:00'),108)
, CONVERT(char(5),DATEADD(minute,
MIN(DATEDIFF(minute,'12:00',Time)/5), '12:05'),108)
, SUM("Money")
FROM Test
GROUP BY DATEDIFF(minute,'12:00',"Time")/5
DROP TABLE Test
I guess you have something to study now :-)
Gert-Jan
rudolf.ball@.asfinag.at wrote:
> Dear NG,
> I want to query a table like this:
> Customer | Money | Time
> A 12 12:01
> B 17 12:03
> C 25 12:06
> D 14 12:09
> E 10 12:11
> ...
> My target is to have the SUM of Money Transfers by TIME SLICE, eg.
> 12:00 - 12:05: 29
> 12:05 - 12:10: 39
> 12:10 - 12:15: 10
> ...
> How can I query this?
> Thank you very much
> Rudi|||With SQL 2005, you could create a mapping from time to some integer domain
(say, convert to minutes in the day and divide by 5), then use the AVG(col)
OVER (PARTITION BY integerslice) functionality that was added. This avoids
having to write all of the slices out in a big case statement.
good luck.
Conor
<rudolf.ball@.asfinag.at> wrote in message
news:1141657033.464616.225810@.j33g2000cwa.googlegroups.com...
> Thank you very much,
> but if I want to split a DAY into 5-MIN-SLICES, how can I do this:
> CASE?
> Thank you
> Rudi
>