Grouping problem

I need to provide a summary row at the bottom of each group that sums accounts of a specific type and subtracts it from the group total.

Acct Balance Prior Balance

A 10 5

B 4 8

C 7 6

D 9 12

Total 30 31

exp accts 13 20

Final 17 11

Is there an easy way to do this in reporting services?

So you want to perform a conditional sum based on another field (account type) in the dataset?

You need add some additional rows to the group footer and put an Iif statement inside the Sum that returns the value to aggregate for a match and 0 otherwise e.g.

Sum(Iif(Fields!account_type.Value = "exclude", Fields!Balance.value, 0))

Then in the last row subtract one row from the other.

The following is RDL code for an example:

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="master">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=.;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>2dd60f19-bf57-4e66-80c3-288d239d3f80</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>21cm</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Table Name="table1">

<Footer>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox7">

<rd:DefaultName>textbox7</rd:DefaultName>

<ZIndex>5</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Total</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance_1">

<rd:DefaultName>balance_1</rd:DefaultName>

<ZIndex>4</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance_1">

<rd:DefaultName>prior_balance_1</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!prior_balance.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox4">

<rd:DefaultName>textbox4</rd:DefaultName>

<ZIndex>8</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>exc accts</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox5">

<rd:DefaultName>textbox5</rd:DefaultName>

<ZIndex>7</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox6">

<rd:DefaultName>textbox6</rd:DefaultName>

<ZIndex>6</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox8">

<rd:DefaultName>textbox8</rd:DefaultName>

<ZIndex>11</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>Final</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox9">

<rd:DefaultName>textbox9</rd:DefaultName>

<ZIndex>10</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox10">

<rd:DefaultName>textbox10</rd:DefaultName>

<ZIndex>9</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Sum(Fields!prior_balance.Value) - sum(Iif(Fields!type.Value = 1, Fields!prior_balance.Value, 0))</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Footer>

<DataSetName>DataSet1</DataSetName>

<Top>1.5cm</Top>

<Width>6.5cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="account">

<rd:DefaultName>account</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!account.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="balance">

<rd:DefaultName>balance</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="prior_balance">

<rd:DefaultName>prior_balance</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!prior_balance.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>14</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>account</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>13</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>balance</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>12</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Center</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>prior balance</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.63492cm</Height>

</TableRow>

</TableRows>

</Header>

<TableColumns>

<TableColumn>

<Width>2cm</Width>

</TableColumn>

<TableColumn>

<Width>1.75cm</Width>

</TableColumn>

<TableColumn>

<Width>2.75cm</Width>

</TableColumn>

</TableColumns>

<Height>3.1746cm</Height>

</Table>

</ReportItems>

<Height>5cm</Height>

</Body>

<rd:ReportID>2e9a4c57-83bc-4a17-9dcd-287d47e72e3d</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select 'A' as account, 1 as type, 10 as balance, 5 as prior_balance

union

select 'B', 2, 4, 8

union

select 'C', 1, 7, 6

union

select 'D', 2, 9, 12</CommandText>

<DataSourceName>master</DataSourceName>

</Query>

<Fields>

<Field Name="account">

<rd:TypeName>System.String</rd:TypeName>

<DataField>account</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="balance">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>balance</DataField>

</Field>

<Field Name="prior_balance">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>prior_balance</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>16cm</Width>

<InteractiveHeight>29.7cm</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

Group sums wrong in Matrix

Banging my head against the wall on this one.
Running a matrix report in RS 2000. I have 2 groupings, Department and
Company. I want to get a sum of total hours for each job code and
Department and Company. The job code hours show up in the detail, as
=Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
sum(Fields!HoursTotal.Value ))
When I run the report, the sum for the department is off.
Job A 8.0
Job B 0.0
Job C 8.2
Job D 71.1
Job E 99.5
--
Group Sum 186.7 (should be 186.8)
Any idea of what is wrong?
The Stored procedure returns values to 1 decimal place as a double.
Joewhy do you want to use:
Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
sum(Fields!HoursTotal.Value ))
can you try
Iif (sum(Fields!HoursTotal.Value) = 0 , 0,
sum(Fields!HoursTotal.Value ))
or
Iif (sum(Fields!HoursTotal.Value) = 0.0 , 0,
sum(Fields!HoursTotal.Value ))
to see what happen?
in format of group sum, set it to n2 and see what happen?
"Jsarna" wrote:
> Banging my head against the wall on this one.
> Running a matrix report in RS 2000. I have 2 groupings, Department and
> Company. I want to get a sum of total hours for each job code and
> Department and Company. The job code hours show up in the detail, as
> =Iif (Len(sum(Fields!HoursTotal.Value)) = 0 , 0,
> sum(Fields!HoursTotal.Value ))
> When I run the report, the sum for the department is off.
> Job A 8.0
> Job B 0.0
> Job C 8.2
> Job D 71.1
> Job E 99.5
> --
> Group Sum 186.7 (should be 186.8)
> Any idea of what is wrong?
> The Stored procedure returns values to 1 decimal place as a double.
> Joe
>

group several rows in a SQL query

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

id value
1 1
2 3
3 5
4 7
5 9

The result I expect from the query are 3 rows: [9, 15,21]:
1st row: 1+3+5 = 9
2nd row: 3+5+7 = 15
3rd row: 5+7+9 = 21

Help is appreciated,
Guidoa few sql commands? how about just one? :)select this.value + prev.value + next.value as result
from daTable as this
inner
join daTable as prev
on prev.id =
( select max(id)
from daTable
where id < this.id )
inner
join daTable as next
on next.id =
( select min(id)
from daTable
where id > this.id )|||Thanks for your response, its awesome, but I dont think it fits my needs. I gave an example with just three consecutive rows accumulated, in real life it will be hundreds of rows.
The number of consecutive rows isnt fixed, either, it depends on user input.

Thank you anyway,
Guido|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

i'm done in this thread|||Asking the question you want anwsered? Hmm....a radical concept.

This code will work provided your IDs are consecutively numbered with no gaps:

declare @.RangeLength
set @.RangeLength = 3

select t1.id,
sum(t2.value)
from [YourTable] t1
inner join [YourTable] t2 on t2.id between t1.id-@.RangeLength+1 and t1.id
group by t1.id

If your IDs are not uniformly sequential, then things get more complex, and might best be solved using a temporary table.|||here's a tip in case you wish to continue to ask questions -- ask your real question, not a different one

I did... not as clearly as I should have, but I did:

Hello,

is it possible to perform the following task with a few SQL commands? I have a table with many rows and I want to retrieve all sums of three (any) consecutive row values:

@.blindman:
Yes, my IDs are consecutively numbered, without gaps. Ill try your solution, many thanks.

Guido|||Maybe this does what you want:WITH t(value, nr) AS
(SELECT value, (ROW_NUMBER() OVER (ORDER BY id) - 1)/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nr(Just replace 17 by the number of rows you want to group.)|||Alternatively, if your SQL doesn't have ROW_NUMBER(), and/or if your "id" column just contains all numbers 1, 2, 3 etc. (which means they already contain the ROW_NUMBER()s), the following gives the same result:WITH t(value, nr) AS
(SELECT value, id/17 AS nr
FROM myTable)
SELECT SUM(value)
FROM t
GROUP BY nrOr possibly even, if your SQL allows grouping by expression:SELECT SUM(value)
FROM myTable
GROUP BY id/17or possibly (in case your SQL doesn't perform integer division):SELECT SUM(value)
FROM myTable
GROUP BY CAST(id/17 AS int)|||Thanks Peter,

I will test your script, too.|||Guido

The following seems to work fine and may be easier to understand.
Again the id's need to be sequential but I believe you said they were.

Mike

select t1.val + t2.val + t3.val as vals
from my_table t1,
my_table t2,
my_table t3
where t1.id < t2.id
and t2.id < t3.id
and t3.id < t1.id + 3;|||Easier to understand than a single join? I don't think so.
Plus, you need to read GNiewerth's second post. Your solution is not scalable.|||you need to read GNiewerth's second post. Your solution is not scalable.
You're right - I didn't see that requirement - please ignore my code then!
Mike

group footer and table footer sums used in % formula

I'm having a hard time understanding how to use my group footer Sum to
be used in a formula with my table footer Sum to get a % (group) of
total (table)? They are both text boxes Sum(Fields!loanamount.Value).
These are not fields that I can grab and put into an expression. How do
I proceed? Thanks for any help.Your expression would be:
=Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
"datasetForTABLE") * 100
Where group1NAME is the name of the grouping in which you are summing - this
is called the SCOPE. The name of the dataset that you bound to the TABLE
itself is what you will scope for "datasetForTABLE" (be sure to include the
quotes).
=-Chris
"nancy" <northtexassupply@.yahoo.com> wrote in message
news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> I'm having a hard time understanding how to use my group footer Sum to
> be used in a formula with my table footer Sum to get a % (group) of
> total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> These are not fields that I can grab and put into an expression. How do
> I proceed? Thanks for any help.
>|||Thanks. That helped alot.
Chris Conner wrote:
> Your expression would be:
> =Sum(Fields!loanamount.Value, "group1NAME") / Sum(Fields!loanamount.Value,
> "datasetForTABLE") * 100
> Where group1NAME is the name of the grouping in which you are summing - this
> is called the SCOPE. The name of the dataset that you bound to the TABLE
> itself is what you will scope for "datasetForTABLE" (be sure to include the
> quotes).
> =-Chris
> "nancy" <northtexassupply@.yahoo.com> wrote in message
> news:1162396774.944155.35460@.i42g2000cwa.googlegroups.com...
> > I'm having a hard time understanding how to use my group footer Sum to
> > be used in a formula with my table footer Sum to get a % (group) of
> > total (table)? They are both text boxes Sum(Fields!loanamount.Value).
> > These are not fields that I can grab and put into an expression. How do
> > I proceed? Thanks for any help.
> >

Group data in query and avoid using SP

I have some data that looks like the following and I want to group it by
accountID so that I can get the sums of PaidAmt and charge. The problem is
that I have different paymentDates so I can't group it. And I can't
eliminate paymentDate because the valid date for each accountID is needed.
So, for example, the first group total would be: 32482 | 2005-12-30 | 20.00
| 172.00. I also need to create a separate field that shows the sum of
PaidAmt - sum of charge (per accountID) but I figure that I could do that
calc with Crystal Reports.
This can be done using a SP but I don't want to write unless it's completely
necessary.
Thanks.
accountid paymentDate PaidAmt charge
-- ---
32482 2005-12-30 00:00:00.000 20.0000 145.0000
32482 NULL .0000
12.0000
32482 NULL .0000
15.0000
32498 2005-12-29 00:00:00.000 25.0000 145.0000
32498 NULL .0000
12.0000
32498 NULL .0000
15.0000
32658 2005-12-30 00:00:00.000 15.0000 145.0000
32658 NULL .0000
12.0000
32658 NULL .0000
15.0000
32691 2005-12-22 00:00:00.000 15.0000 145.0000
32691 NULL .0000
12.0000
32691 NULL .0000
15.0000
32705 2005-12-23 00:00:00.000 10.0000 135.0000
32705 NULL .0000
12.0000
32705 NULL .0000
15.0000You can do this by creating a subquery to calc the sums by accountid, and
then creating a second query to attach the date. For example
CREATE VIEW vsubAmtPaid AS
SELECT accountid, SUM(PaidAmt) AS totalPaid, SUM(charge) AS totalCharge
FROM table1
GROUP By accountid
CREATE VIEW vwAmtPaid AS
SELECT t.accountid, paymentDate, totalPaid, totalCharge
FROM table1 t INNER JOIN vsubAmtPaid v ON t.accountid = v.accountid
WHERE NOT (paymentDate IS NULL)
I haven't tested this, but it should get you the results you posted, with
one exception. You mentioned you needed a separate field for the sum of the
amount paid and amount charged, but those totals are 20 and 172 respectively
.
Did you by chance want the original, un-summed amounts to be included in
addition to the sums?
Also, I'm assuming that the final results should return only one record per
accountid. If that's not the case, eliminate the WHERE clause from the final
query. Also, based on your sample data I assumed each accountid has only one
record where the paymentDate is not null, so I used that in the second query
to filter on. If this is not the case, you'll need to figure another way to
filter.
"VMI" wrote:

> I have some data that looks like the following and I want to group it by
> accountID so that I can get the sums of PaidAmt and charge. The problem is
> that I have different paymentDates so I can't group it. And I can't
> eliminate paymentDate because the valid date for each accountID is needed.
> So, for example, the first group total would be: 32482 | 2005-12-30 | 20.0
0
> | 172.00. I also need to create a separate field that shows the sum of
> PaidAmt - sum of charge (per accountID) but I figure that I could do that
> calc with Crystal Reports.
> This can be done using a SP but I don't want to write unless it's complete
ly
> necessary.
> Thanks.
> accountid paymentDate PaidAmt charge
> -- ---
> 32482 2005-12-30 00:00:00.000 20.0000 145.0000
> 32482 NULL .0000
> 12.0000
> 32482 NULL .0000
> 15.0000
> 32498 2005-12-29 00:00:00.000 25.0000 145.0000
> 32498 NULL .0000
> 12.0000
> 32498 NULL .0000
> 15.0000
> 32658 2005-12-30 00:00:00.000 15.0000 145.0000
> 32658 NULL .0000
> 12.0000
> 32658 NULL .0000
> 15.0000
> 32691 2005-12-22 00:00:00.000 15.0000 145.0000
> 32691 NULL .0000
> 12.0000
> 32691 NULL .0000
> 15.0000
> 32705 2005-12-23 00:00:00.000 10.0000 135.0000
> 32705 NULL .0000
> 12.0000
> 32705 NULL .0000
> 15.0000
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:592684
On Mon, 27 Mar 2006 11:38:24 -0500, "VMI" <vonchi_m AT yahoo DOT com>
wrote:

>I have some data that looks like the following and I want to group it by
>accountID so that I can get the sums of PaidAmt and charge. The problem is
>that I have different paymentDates so I can't group it. And I can't
>eliminate paymentDate because the valid date for each accountID is needed.
>So, for example, the first group total would be: 32482 | 2005-12-30 | 20.00
>| 172.00. I also need to create a separate field that shows the sum of
>PaidAmt - sum of charge (per accountID) but I figure that I could do that
>calc with Crystal Reports.
>This can be done using a SP but I don't want to write unless it's completel
y
>necessary.
Hi VMI,
If the data you present here is representative for your real data, you
can use:
SELECT accountid, MAX(paymentDate), SUM(PaidAmt), SUM(charge)
FROM YourTable
GROUP BY accountid
(untested - see www.aspfaq.com/5006 if you prefer a tested reply).
Hugo Kornelis, SQL Server MVP

GROUP BY Question

I need to generate daily aggregates (sums) from some hourly data and althoug
h
I have query that works, I think I've gone overboard on the GROUP BY
statement. Can anyone give me some feedback on this? I'm sure there is a
better way to do this, but I'm at a loss.
SELECT
max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
FROM LOT_PRODUCTION LP
WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
(DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
LP.NO_FINISH_LINE = 'L2'
GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
ORDER BY DT_FINISHED ASC;
Thanks in advance,
RaulRaul
Please post DDL+ sample data + expected result
"Raul" <raul@.nothere.com> wrote in message
news:7DE39B83-F304-463C-97E2-C353571F6892@.microsoft.com...
>I need to generate daily aggregates (sums) from some hourly data and
>although
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||I think you group by cluase can be just
GROUP BY convert(varchar(10),DT_FINISHED,101)
--check the syntax of convert though ;)|||Try:
SELECT
max(LP.NO_FINISH_LINE) as NO_FINISH_LINE,
CONVERT(CHAR(10), LP.DT_FINISHED, 126) as DT_FINISHED,
CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) as NO_WEIGHT_PROD,
CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) as NO_WEIGHT_REJECT,
FROM
dbo.LOT_PRODUCTION LP
WHERE
(LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1, (DATEADD(dd, 1 -
DAY(GETDATE()), GETDATE()))), 126) AND GETDATE())
AND LP.NO_FINISH_LINE = 'L2'
GROUP BY
-- DAY(DT_FINISHED),MONTH(DT_FINISHED),YEA
R(DT_FINISHED)
CONVERT(CHAR(10), LP.DT_FINISHED, 126)
ORDER BY
DT_FINISHED ASC;
go
AMB
"Raul" wrote:

> I need to generate daily aggregates (sums) from some hourly data and altho
ugh
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Why not just group by
CONVERT(CHAR(10),LP.DT_FINISHED,126)
and remove the max from your select list for this column?
"Raul" <raul@.nothere.com> wrote in message
news:7DE39B83-F304-463C-97E2-C353571F6892@.microsoft.com...
> I need to generate daily aggregates (sums) from some hourly data and
although
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Thank you very much.
This was very helpful, and I have incorporated the suggested changes into
the query.
Thanks again,
Raul
"Raul" wrote:

> I need to generate daily aggregates (sums) from some hourly data and altho
ugh
> I have query that works, I think I've gone overboard on the GROUP BY
> statement. Can anyone give me some feedback on this? I'm sure there is a
> better way to do this, but I'm at a loss.
> SELECT
> max(LP.NO_FINISH_LINE) NO_FINISH_LINE,
> CONVERT(CHAR(10),max(LP.DT_FINISHED),126) DT_FINISHED,
> CAST(sum(LP.NO_WEIGHT_PROD) as DECIMAL(8,2)) NO_WEIGHT_PROD,
> CAST(sum(LP.NO_WEIGHT_REJECT) as DECIMAL(8,2)) NO_WEIGHT_REJECT,
> FROM LOT_PRODUCTION LP
> WHERE (LP.DT_FINISHED BETWEEN CONVERT(CHAR(10), DATEADD(mm, - 1,
> (DATEADD(dd, 1 - DAY(GETDATE()), GETDATE()))), 126) AND GETDATE()) AND
> LP.NO_FINISH_LINE = 'L2'
> GROUP BY DAY(DT_FINISHED),MONTH(DT_FINISHED),YEAR
(DT_FINISHED)
> ORDER BY DT_FINISHED ASC;
> Thanks in advance,
> Raul|||Why are you formatting the data in the query? That is always done in
the front end. Why are you computing all those silly things in SQL,
which is not a computational language?
Because you are thinking like a COBOL programmer whose data is stored
in strings; think like an SQL programmer who works with abstract data
types.
First build an auxiliary table with your reporting periods. Remember,
tables and not computations:
CREATE TABLE ReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY,
rpt_start_date DATETIME NOT NULL,
rpt_end_date DATETIME NOT NULL,
CHECK (rpt_start_date < rpt_end_date));
Next, do a simple join to get your reports:
SELECT R.report_period_name,
MAX(LP.finish_line_nbr)AS finish_line_max,
SUM(LP.prod_wgt) AS prod_wgt_tot,
SUM(LP.reject_wgt) AS reject_wgt_tot,
FROM LotProduction AS LP,
ReportPeriods AS R
WHERE LP.finish_date
BETWEEN R.rpt_start_date AND R.rpt_end_date
GROUP BY R.report_period_name;
Since your data element did not follow ISO-11179 rules, I tried to
guess at corrections. You might also want to start writing Standard SQL
instead of dialect CURRENT_TIMESTAMP instead of the old getdate(),
CAST() instead of CONVERT(), etc.