Getting sub totals in groups

Hi,
i have a table and a group in it..
i need to have subtotal column..
Im using the cost filed for grouping ...
i need to sum this filed and show it...
it seems that when i use the sum function alone,it sums all the cost values
ignoring the group
example
Project Reg ot Cost total cost
1 2 0 4 8
2 3 0 5 15
totals 5 o 13
isince there are 2 projects with cost 4 ,it sums incorrectly as 13,i
expect it to be 9...
any help please...
Thanks,Try adding a scope to your aggregate =Sum(Fields!Cost.Value, "Group_Name")
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:D582E6A9-B408-458C-AFC1-EFE709B0BD1D@.microsoft.com...
> Hi,
> i have a table and a group in it..
> i need to have subtotal column..
> Im using the cost filed for grouping ...
> i need to sum this filed and show it...
> it seems that when i use the sum function alone,it sums all the cost
> values
> ignoring the group
> example
> Project Reg ot Cost total cost
> 1 2 0 4 8
> 2 3 0 5 15
> totals 5 o 13
> isince there are 2 projects with cost 4 ,it sums incorrectly as
> 13,i
> expect it to be 9...
> any help please...
> Thanks,
>|||CCP,
If you've got you're group set properly (to project) you shouldn't need
to worry about scope. The sum should be in the group footer. In fact
you shouldn't have to specify 'Sum' at all, just dragging the Cost
field from the field list into a cell on the Group footer will sort the
syntax out for you.
What you are getting looks like you have it set in the TABLE footer,
not the GROUP footer.
Chris
Douglas McDowell wrote:
> Try adding a scope to your aggregate =Sum(Fields!Cost.Value,
> "Group_Name") --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "CCP" <CCP@.discussions.microsoft.com> wrote in message
> news:D582E6A9-B408-458C-AFC1-EFE709B0BD1D@.microsoft.com...
> > Hi,
> > i have a table and a group in it..
> > i need to have subtotal column..
> > Im using the cost filed for grouping ...
> > i need to sum this filed and show it...
> > it seems that when i use the sum function alone,it sums all the
> > cost values
> > ignoring the group
> > example
> > Project Reg ot Cost total cost
> > 1 2 0 4 8
> > 2 3 0 5 15
> >
> > totals 5 o 13
> >
> > isince there are 2 projects with cost 4 ,it sums incorrectly
> > as 13,i
> > expect it to be 9...
> >
> > any help please...
> >
> > Thanks,
> >

Getting mor recursive value in a select

I need to get the value of the filed in mytable that appeears more often.
myfield1:
10
20
10
30
40
10
10
50
60
70
20
30
10
I need a query tha get me 10.
ThanksAm Wed, 5 Oct 2005 07:39:06 -0700 schrieb checcouno:

> I need to get the value of the filed in mytable that appeears more often.
> myfield1:
> 10
> 20
> 10
> 30
> 40
> 10
> 10
> 50
> 60
> 70
> 20
> 30
> 10
> I need a query tha get me 10.
> Thanks
select myfield1 from mytable group by myfield1 having count(*) > 1
bye,
helmut|||SELECT col
FROM tbl
GROUP BY col
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM tbl
GROUP BY col) ;
Don't forget that there could be more than one value that is the most
fequent unless you have business rules to prevent this.
David Portas
SQL Server MVP
--
"checcouno" wrote:

> I need to get the value of the filed in mytable that appeears more often.
> myfield1:
> 10
> 20
> 10
> 30
> 40
> 10
> 10
> 50
> 60
> 70
> 20
> 30
> 10
> I need a query tha get me 10.
> Thanks
>|||Isn't best
select top 1 myfield1 from mytable group by myfield1 order by count(*)
?
Thanks
"helmut woess" wrote:

> Am Wed, 5 Oct 2005 07:39:06 -0700 schrieb checcouno:
>
> select myfield1 from mytable group by myfield1 having count(*) > 1
> bye,
> helmut
>|||...order by count(*) DESC (of course!)
"David Portas" wrote:
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> Don't forget that there could be more than one value that is the most
> fequent unless you have business rules to prevent this.
> --
> David Portas
> SQL Server MVP
> --
> "checcouno" wrote:
>|||Which query is more quick?
SELECT col
FROM tbl
GROUP BY col
HAVING COUNT(*) >= ALL
(SELECT COUNT(*)
FROM tbl
GROUP BY col) ;
or
select top 1 myfield1 from mytable group by myfield1 order by count(*) DESC
?
I need only one value, i don't mind if there is more than one value
Thanks|||The 2nd one will be quicker, as the first is doing two scans.
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:55864B3A-1DF3-4204-B03C-36618B0F9AA4@.microsoft.com...
> Which query is more quick?
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> or
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
DESC
> ?
> I need only one value, i don't mind if there is more than one value
> Thanks
>
>
>|||> Isn't best
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
> ?
No, because the result is undefined if you have ties. It you use TOP then
use the WITH TIES option.
David Portas
SQL Server MVP
--
"checcouno" wrote:
> Isn't best
> select top 1 myfield1 from mytable group by myfield1 order by count(*)
> ?
> Thanks
>
> "helmut woess" wrote:
>|||> Which query is more quick?
They give different results so that doesn't seem like a sensible comparison.
Regardless of that, performance is something you should test on your own
system with your data.

> I need only one value, i don't mind if there is more than one value
That seems like a contradiction but I think you mean you want any ONE value
rather than more than one. That's not necessarily a problem but it makes
sense to ensure that your query returns a well defined result rather than
"any one row at random". Random results are hard to test and tend to look
like bugs. Amend your specification accordingly if possible - even if that's
just to specify the minimum of the possible values or the first one when the
y
are sorted in some fixed order.
David Portas
SQL Server MVP
--
"checcouno" wrote:

> Which query is more quick?
> SELECT col
> FROM tbl
> GROUP BY col
> HAVING COUNT(*) >= ALL
> (SELECT COUNT(*)
> FROM tbl
> GROUP BY col) ;
> or
> select top 1 myfield1 from mytable group by myfield1 order by count(*) DES
C
> ?
> I need only one value, i don't mind if there is more than one value
> Thanks
>
>
>