Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

Grouping several items in one group

Hi Everyone,
I am new to reporting services and I am trying to create groups which
contains more then one code .
Table
Name, Code, Amount
paper 1101 £10
Pens 1102 £5
Shoes 2512 £20
Clothes 3455 £5
I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
group 2 with total.
At the moment I can only seem to group each one individually.
Please help.
John
--
John HoYou question is more of a SQL problem, and there is more than one way
to solve your problem.
SELECT 'GRP1' as groupcode, amount from paper where code =3D 1101
UNION
SELECT 'GRP1' as groupcode, amount from pens where code =3D 1102
UNION
SELECT 'GRP2' as groupcode, amount from shoes where code =3D 2512
UNION
SELECT 'GRP2' as groupcode, amount from clothes where code =3D 3455
save the above query to a View object. When you open the view, you'll
see this:
<pre>
groupcode | amount
GRP1 | =A310
GRP1 | =A35
GRP2 | =A320
GRP2 | =A35
</pre>
Now you can group & sum on your view for your report. I'm sure there
are more elegant solutions (perhaps using StoredProcs), but this is
dirty and quick...heh.
On Apr 7, 11:05 am, Learner <Lear...@.discussions.microsoft.com> wrote:
> Hi Everyone,
> I am new to reporting services and I am trying to create groups which
> contains more then one code .
> Table
> Name, Code, Amount
> paper 1101 =A310
> Pens 1102 =A35
> Shoes 2512 =A320
> Clothes 3455 =A35
> I want to put code 1101 and 1102 as group 1 with total, 2512 and 3455 as
> group 2 with total.
> At the moment I can only seem to group each one individually.
> Please help.
> John
> --
> John Ho

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

grouping by partial string

Hello All,

I am looking for an expression for a group in a matric. I am trying to figure out how to group by the a certain amount of letters in a string. For example if I have the followong fields I am grouping...

Bob001

Bob

Robert005

Doug053

Doug100

Douglas

Barney001

Frank

I want to group it up as...

Bob

Doug

Barney

Frank

And then be able to summarize the results in the matrix.

Thanks in advance for any help

-Clint

If the last 3 characters are always 3 digits then you can simply group by left(Name, len(Name) - 3)

Adamus

|||

Just be sure your SELECT left(Name, len(Name) - 3) contains the trim.

Adamus

|||Unfortunately they do not all end in 3 digits. Ill fix my example. thanks though|||

Ok next question:

If digits exist, will they always be 3 digits on the tail end of the name?

If so you can check CASE Name WHEN isnumeric(right(Name, 3)) ...

If not, you'll probably want to use regular expressions to grab the text only portion.

In that case, you'll have to create a UDF which you can find here

Adamus

|||

Thanks Adamus,

Yes, when there are numbers present at the end, there are always three.

What would the expression look like?

Thanks much

Clint

|||

CREATETABLE #temp(myName varchar(20))

INSERTINTO #temp SELECT'Bob001'

INSERTINTO #temp SELECT'Bob'

INSERTINTO #temp SELECT'Robert001'

INSERTINTO #temp SELECT'Robert'

SELECT*FROM

(

SELECTCASEWHENISNUMERIC(RIGHT(myName, 3))= 1

THENLEFT(myName,LEN(myName)- 3)ELSE myName ENDAS [Result]

FROM #temp

)as t

GROUPBY Result

DROPTABLE #temp

Adamus

|||

What happened to Robert in the second list? Was he fired? Did he quit?

On A serious note, someone posted the code you need to remove the digits...

though it does not appear that it will solve the problem as "Douglas" has no digits on the end of it.

What you are looking for, sounds to me is some type of pattern matching...

|||

nice touch.. Mr. Turner...

I hope he actually uses the correct sql to do a select into the temp table instead of doing it all literally.

|||Thanks for the help. I am trying to accomplish in reporting services itself, not in the dbase. From what I gather there is now expression that would do the same thing. Again,I appreciate the help.|||

You can do this in SQL in the DataSet instead of the database or you can do in an expression using the Len function to find out how long the initial string is, the InStr function to find the location of a number, and then the Mid function to extract a string that is the Total Length long minus the Index value of the number that was found. Wrap all this in an IIF to handle the cases when no number is present.

HTH

Dean

|||

After regrouping at another field that seperated out the fields ending in - 083, i was able to get it working with your expression modified. Thanks so much for your help. Here is what ended up working...

Code Snippet

=iif(Fields!BankNumber.Value="083",left(Fields!TestName.Value, len(Fields!TestName.Value) - 11),Fields!TestName.Value)

|||oops. turns out not to work quite right after all. I errors out the first field name but works for the rest. I get a warning saying that the Length statement must be equal to or greater than 0. Since we are using a negative number, it is popping a warning.|||

Exactly. Im trying to figure out how to trim the last 11 characters off certain fields. My real life example has some fields ending in - Bank 083 (or another 3 numbers). i am thinking of the "LIKE" to seach for the pattern and remove it for group sake. Anyone have an idea on that one?

Thanks,

Clint

Grouping a report

I am trying to group a report like so:
Company: ABC
Trip # Leg Amount
1 1 100
1 2 200
Sub Total 300
Trip # Leg Amount
2 1 100
2 2 200
Sub Total 300
Company: DEF
Trip # Leg Amount
3 1 100
3 2 200
Sub Total 300
Trip # Leg Amount
4 1 100
4 2 200
Sub Total 300
I get get the detail info in the report just fine with a grid. But
I'm running into problems when trying to add the company into the
grid. Currently I have a group created to get the sub total for each
trip and that works fine. But when I create the group to add the
company into the grid, it keeps putting the header columns (Trip #,
Leg, Amount, etc) above the company name. I have tried everything I
can think of to get the company name above it but can't get it to work.if you right click on the group row's headers(the grey part on the far
left when you have the table focused) you can insert a new group.
have a group with the company as the expression. then another with
the trip as the expression.
* company
* trip
** DETAILS|||I got it to work doing that and putting the column header row inside
my group row (instead of in a header row). But now I have another
question - not sure if this is possible, but since I am in a grid, my
Company name ends up in the same column as my trip number. The
company name is obviously going to be bigger than the trip number, but
if I stretch out the column to handle that, then the leg box looks
much larger than it needs to be (I'll try to show it here, but it'll
be tough):
Is there some way I can change the spacing on the company column
without affecting the leg column?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Company: xx ABCDEFGHIJKLMNOPQ xx x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Trip # xx Leg xx Amount x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 1 xx
100 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 2 xx
200 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x xx Sub Total xx 300
x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Group by Top # entered in as Parameter

Background: I have a report that groups by Item number and gives adds
up total amount for that item number. What I want to do is have the
user enter in a numeric value as a parameter such as 10, 15, 20, etc
that will then only display the TOP 10, 15, 20, etc (what they entered
in the parameter) total amounts on the report. Can anyone help me out,
Im sure this can be done but it gets tricky with the parameters thrown
in the mix. Any suggestions is much appreciated. Thanks!hi brent
you can do this w/o issue by using a stored procedure as the source dataset
(and having your 'TOP' value included as one of the parameters).
next, you are going to need to supply a dataset for the dropdown:
select '10' as topval
union
select '20' as topval
union
select '3....
if you plan on 'rolling your own' ASP.NET interface, you can preload the
values for the dropdown in HTML.
Rob
"Brent" wrote:
> Background: I have a report that groups by Item number and gives adds
> up total amount for that item number. What I want to do is have the
> user enter in a numeric value as a parameter such as 10, 15, 20, etc
> that will then only display the TOP 10, 15, 20, etc (what they entered
> in the parameter) total amounts on the report. Can anyone help me out,
> Im sure this can be done but it gets tricky with the parameters thrown
> in the mix. Any suggestions is much appreciated. Thanks!
>

Group by query

I have this table ....

AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC

1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR

1002 ZX 1/1/2006 21 DR

1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC

Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006

AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21

--
1003 ABC 23 0 0

One row for each name,that is group up by will be on AID and NAME.

Any help will be greatly appreciated...create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.|||create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.
Sorry ,I think that will not work at all.I mean if you use case in that case you have to use group by on AID,NAME,Type.
But That will give you multiple rows, which I don't want...
I think its not so easy man... ;)|||I think its not so easy man...
yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!|||yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME
:rolleyes: Oh my God !! Yes thats it ...I haven't thought that ...:rolleyes:
If you put the sum within the case, you have to use Type in Group by...hmmm thats it
Superb !!
You have put the case within Sum(), WOW!!
Rudy you are great !!|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!

Anyways, Thanks Wash for your help.:)

Group By problem, how to?

Hi, The following works:
Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
From SomeTable
Group By YEAR(TransDate)
The following gives an error, I have to group by Year and Month,
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
The problem is that TransDate is now used twice in the Group By clauseChris,
Why is this a problem? If you want one result row for
each year and month combination, you need to group on
year and month, or alternatively, use a single expression
for the year and month in both the select and group by
clauses.
The code you gave works correctly with no error
on SQL Server 2000 and 2005. An alternative with
a single group by item is given below also:
create table SomeTable (
TransDate datetime,
Amount money
)
insert into SomeTable values ('20050403', $100)
insert into SomeTable values ('20050703', $200)
insert into SomeTable values ('20060703', $300)
insert into SomeTable values ('20050708', $400)
Select SUM(Amount) As Total,
YEAR(TransDate) As TheYear,
MONTH(TransDate) As TheMonth
From SomeTable
Group By YEAR(TransDate), MONTH(TransDate)
go
Select
Total,
YEAR(TransMonth) As TheYear,
MONTH(TransMonth) As TheMonth
From (
Select
SUM(Amount) AS Total,
DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
FROM SomeTable
GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
) M
GO
drop table SomeTable
Steve Kass
Drew University
Chris Botha wrote:

>Hi, The following works:
>Select SUM(Amount) As Total, YEAR(TransDate) As TheYear
>From SomeTable
>Group By YEAR(TransDate)
>The following gives an error, I have to group by Year and Month,
>Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
>From SomeTable
>Group By YEAR(TransDate), MONTH(TransDate)
>The problem is that TransDate is now used twice in the Group By clause
>
>|||Hi Steve, you are right, it works, must have been a typo somewhere.
Sorry for wasting your time (shrink, shrink, shrink).
Chris.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uIXBMjvOGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Chris,
> Why is this a problem? If you want one result row for
> each year and month combination, you need to group on
> year and month, or alternatively, use a single expression
> for the year and month in both the select and group by
> clauses.
> The code you gave works correctly with no error
> on SQL Server 2000 and 2005. An alternative with
> a single group by item is given below also:
> create table SomeTable (
> TransDate datetime,
> Amount money
> )
> insert into SomeTable values ('20050403', $100)
> insert into SomeTable values ('20050703', $200)
> insert into SomeTable values ('20060703', $300)
> insert into SomeTable values ('20050708', $400)
> Select SUM(Amount) As Total,
> YEAR(TransDate) As TheYear,
> MONTH(TransDate) As TheMonth
> From SomeTable
> Group By YEAR(TransDate), MONTH(TransDate)
> go
> Select
> Total,
> YEAR(TransMonth) As TheYear,
> MONTH(TransMonth) As TheMonth
> From (
> Select
> SUM(Amount) AS Total,
> DATEADD(month,DATEDIFF(month,0,TransDate
),0) AS TransMonth
> FROM SomeTable
> GROUP BY DATEADD(month,DATEDIFF(month,0,TransDate
),0)
> ) M
> GO
> drop table SomeTable
> Steve Kass
> Drew University
> Chris Botha wrote:
>

Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

------------

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)

Group by on a concatenate field

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by ?

How can I group by the field that I selected as CODE?

you have to repeat the expression:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0' and Service_Prefix like 'F'
Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

|||Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

But... why do you add an empty string in between? And do you mean LIKE 'F%', rather than LIKE 'F' ?

Don't underestimate the power of table expressions. The optimiser knows what you mean (so there's very little difference to performance), and you can easily make something far more readable that way. You could also have used a CTE, like this:

with c as (select CONVERT(VarChar(10),Service_Prefix)+ '' +

CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount,

Service_Prefix from

dbo.Enterprise_Credits_Import_90_days)
select top 100 code, date_issued, sum(amount)

from c
where code <> '0'

and Service_Prefix like 'F'

group by code, date_issued

Rob|||

Rob Farley wrote:

Or if you're interested in readability you could make it:

select top 100 code, date_issued, sum(amount)
from
(select CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, Amount, Service_Prefix from
dbo.Enterprise_Credits_Import_90_days) c
where code <> '0'
and Service_Prefix like 'F'
group by code, date_issued

SQL Server does not allow grouping by column alias names - does it? Other RDB engines allow this, and even ordinal grouping (group by 1,2, ...) notations but I thought SQL Server required you to repeat the expressions.

|||But this isn't actually grouping by a column alias. It's grouping by a field in a table expression. It would be no different if you created a view which had those fields in it - then you wouldn't see a problem using the view's fields to group by...

By wrapping the fields up in a table expression, you can easily circumnavigate the restrictions on SQL Server to repeat those expressions.

Rob|||right - I should have looked more closely at your original query - I didn't see the inline view before...|||:) So has it helped?|||

Im going to test it today. Ill let you know if I get it to work.

Thanks for alll the help !

|||

I tried the first statement and was able to make it work. Thanks for the info.

The second query gave me this error

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

Also due to the fact that the database has around 6 million records the group by's seem to make the query take a while to run. Is there any way to speed up the query?

Thanks again for your help

|||The second one requires SQL2005 - you'll get an error in SQL2000.

Could you get away with grouping by service_prefix and service_code separately, and then only concatenating them in the final select? That way, you could put a useful index on those two fields (plus date_issued, and amount), and it should run much more nicely. A covering index which includes all the fields you're interested in will mean that it doesn't even need to look at the table, because all the info will exist in the index.

Like this:

Select TOP 100 CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) as CODE, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code <> ' 0' and Service_Prefix like 'F%'
group by service_code, service_prefix, date_issued

And you have an index on service_code, service_prefix, date_issued, amount

By changing the where clause to filter on the service_code rather than the concatenated field, that will help performance too, because it can then use the index more effectively. If you need to cater for where the 0 can be either in the prefix or in the code, you could always do an extra check... but try to avoid grouping or filtering on calculated fields, which makes it harder for the system to use indexes.

Rob|||

I think I see what you are saying here. The group by on both non concatenated fields should produce the same results as one group by by using the indexes more effectively. Ill try this and let you know. Prob be tommorow. Although in order to do a group by I thought you had to have the the group by fields in the select statement?

Thanks for your help.

|||You don't need to have the group by fields in the select statement, you just can't use fields that aren't either aggregated or one of the grouped fields.

But you can certainly select a concatenation of two of the grouped fields - definitely no problem there.

Rob|||

I would be careful about grouping on such a value as this:

Group by CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code), Date_Issued

Instead of doing all of this conversion stuff in the bowels of the query do it either:

1. In the user interface and write the query as:

Select TOP 100 Service_Prefix, Service_Code, Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

2. If you cannot use the UI, then do the conversion in an aggregate. There will be little performance hit because there will only be a single row in every case:

Select TOP 100 Max(CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code)) as CODE,
Date_Issued, sum(Amount)
from dbo.Enterprise_Credits_Import_90_days
where Service_Code<> '0'
and Service_Prefix = 'F'
Group by Service_Prefix, Service_Code, Date_Issued

ANY code appearing in an expression in a where or group by clause (and having, join-on criteria, etc) can cause performance issues that cannot be solved with indexes, like the criteria:

CONVERT(VarChar(10),Service_Prefix)+ '' + CONVERT(VarChar(10),Service_Code) <> ' 0'

Will not perform well because you don't have an index on this expression, and the optimizer would have to figure it out over and over instead of a simple probe into an index.

|||Yes... this is the point I was trying to make.

One thing to do it as an intellectual exercise about what you can do with T-SQL, but as soon as you're talking about performance, then you need to consider the fact that you really don't want to use the results of functions for filters/groups/sorts.

Rob|||Not disagreeing with you...Just adding my 2 cents worth to clarify/back you up :)

Group by Month

If I have a date column and want to return a columns that sum by each month of the year, what is the best way to do that?

Example

Date Amount

1/3/2007 10

1/7/2007 15

3/4/2007 8

3/21/2007 19

5/33/2007 12

9/6/2007 5

12/8/20007 4

12/12/2007 10

Return:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Amount 25 0 27 0 12 0 0 0 5 0 0 14

The most convenient way is to use a PIVOT based on the DATEPART of the date using 'MM' -- the month part -- as the target datepart. Another alternative would involve the use of SUM and CASE over 12 different columns again based on a date part.

( My overview sounds like mumbo jumbo to me, too. Can somebody pick me up? )

|||

Here are a couple different approaches, some useful for SQL 2000/2005, and some only for SQL 2005.

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||Thanks, guys. That works for me.

Group breaking prematurely

I have a report that simply shows counts, amount and an extended amount
grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
(st_s_usoc). The problem I am having is the tariff group is prematurely
breaking within the group as shown by the example below. I also included the
sql code behind the report further below. Does anyone have any suggestions
for me as to what to look at that could be possibly causing this break. I
created the same exact report in Crystal Reports and the report is grouping
correctly.
Exch USOC Description Quantity Amount Ext
Amount
258
400R Call Forwarding 2 2.65
5.30
401R Busy Call Forward 2 2.65
5.30
404 Call Waiting 1
6.65 6.65
404 Call Waiting 4
6.65 26.60
(there should be one line for the 404 USOC)
SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
Extended_Amount
FROM vw_VTC_Sub_Tariff_count
WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
ORDER BY exchange, st_s_usoc
Any help would be greatly appreciated.Your post is not very clear . So grouping is done first by exchange
( which is column 1 ? ) and then "extended amount" ( is that column
2 ?) .You have written "there should be one line for the 404 USOC" ,
do you mean that this row has a different exchange and should form a
new group ?Explain the abbreveations a bit, what is USOC ?
Cheers
Shai
On Nov 24, 5:02 am, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext
> Amount
> 258
> 400R Call Forwarding 2 2.65
> 5.30
> 401R Busy Call Forward 2 2.65
> 5.30
> 404 Call Waiting 1
> 6.65 6.65
> 404 Call Waiting 4
> 6.65 26.60
> (there should be one line for the 404 USOC)
> SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> Extended_Amount
> FROM vw_VTC_Sub_Tariff_count
> WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> ORDER BY exchange, st_s_usoc
> Any help would be greatly appreciated.|||Sorry about that, but yes unfortunately what you see on the post doesn't look
exactly like what I had typed. It wrapped the lines a bit. There should be
four lines one for USOC 400R, 401R and two for 404. The problem is the two
404 lines, this should be combined into one line, I can not figure out what
is causing the report to break into two lines. I'm stuck. USOC(stands for
Universal Service ', basically it's a telephone service tariff)
Thank you for attention regarding this.
Jackie
"shaikat.das@.gmail.com" wrote:
> Your post is not very clear . So grouping is done first by exchange
> ( which is column 1 ? ) and then "extended amount" ( is that column
> 2 ?) .You have written "there should be one line for the 404 USOC" ,
> do you mean that this row has a different exchange and should form a
> new group ?Explain the abbreveations a bit, what is USOC ?
> Cheers
> Shai
>
> On Nov 24, 5:02 am, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext
> > Amount
> > 258
> > 400R Call Forwarding 2 2.65
> > 5.30
> > 401R Busy Call Forward 2 2.65
> > 5.30
> > 404 Call Waiting 1
> > 6.65 6.65
> > 404 Call Waiting 4
> > 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> > SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> > Extended_Amount
> > FROM vw_VTC_Sub_Tariff_count
> > WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> > ORDER BY exchange, st_s_usoc
> >
> > Any help would be greatly appreciated.
>|||On Nov 23, 3:02 pm, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext Amount
> 258
> 400R Call Forwarding 2 2.65 5.30
> 401R Busy Call Forward 2 2.65 5.30
> 404 Call Waiting 1 6.65 6.65
> 404 Call Waiting 4 6.65 26.60
> (there should be one line for the 404 USOC)
>
> Any help would be greatly appreciated.
I would check your data in preview filtering on EXCHANGE=404... The
query should return only the 5 rows, then change your SQL to do a
GROUP BY so the server returns the data in the form that you expect.
If you still see two groups with the 404 data, then it's a data
issue.
Make sure that your data fields are not right-padded with spaces, and
that your database converts zero-length strings to NULLs (perhaps the
USOC field, though appearing empty, really isn't). Try playing with
the TRIM command to truncate trailing spaces.
In the Expressions, try an Expression of
= "(" & Fields!ABCXYZ.Value & ")"
to make sure that there aren't any weird characters appended to your
strings.
One thing we ran into recently was Char(191) in a Memo field -- a
"hard space" that in HTML rendered as a space but to a String Compare
(which is all a Matrix grouping is) they are different.
-- Scott|||Thanks Scott for your suggestions,
At least I was able to eliminate the possibility of it being a data issue,
after using your group by suggestion. I am going to forward this issue on to
a R.S. instructor I had and hopefully he knows what might be causing this.
Then perhaps a MSDN incident, who knows may it's a bug.
Thanks again,
Jackie
"Orne" wrote:
> On Nov 23, 3:02 pm, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext Amount
> > 258
> > 400R Call Forwarding 2 2.65 5.30
> > 401R Busy Call Forward 2 2.65 5.30
> > 404 Call Waiting 1 6.65 6.65
> > 404 Call Waiting 4 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> >
> > Any help would be greatly appreciated.
> I would check your data in preview filtering on EXCHANGE=404... The
> query should return only the 5 rows, then change your SQL to do a
> GROUP BY so the server returns the data in the form that you expect.
> If you still see two groups with the 404 data, then it's a data
> issue.
> Make sure that your data fields are not right-padded with spaces, and
> that your database converts zero-length strings to NULLs (perhaps the
> USOC field, though appearing empty, really isn't). Try playing with
> the TRIM command to truncate trailing spaces.
> In the Expressions, try an Expression of
> = "(" & Fields!ABCXYZ.Value & ")"
> to make sure that there aren't any weird characters appended to your
> strings.
> One thing we ran into recently was Char(191) in a Memo field -- a
> "hard space" that in HTML rendered as a space but to a String Compare
> (which is all a Matrix grouping is) they are different.
> -- Scott
>