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 question...

Hi,

I am migrating some reports from MS Access2003 to SQL 2005 Reporting Services.

I have a dataset which contains columns for Sex, Age, Name etc... I firstly display the contents of this dataset in a table and this is fine. I also need to display a table of the breakdown of the age and sex. E.G:-

< 16yrs 16yrs-24yrs 25yrs-64yrs 65yrs-74yrs 75yrs-84yrs >=85yrs
Male x x x x x x

Female x x x x x x

Does anyone know if this is possible. I was going to use a DCount function (As found in access) but I can not find it in SRS. What is thet best way to produce this result?

Thanks in advance for your time

Peter Tewkesbury

BlueFlower Limited

Conditional aggregation can be achieved as follows:

=Sum(iif(Fields!Age.Value >= 25 AND Fields!Age.Value < 65, 1, 0))

-- Robert

sql

Grouping Problem

I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')Never mind...I figured it out
"Phill" wrote:

> I have a table that contains a region, year, part, and quantity. I want t
he
> query output to be one line per region with the years as columns for the s
um
> of the quantities. I've tried this, but I get one line per year instead o
f
> one line per region.
> SELECT
> PART_ID,
> REGION,
> CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
> CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
> CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
> CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
> CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
> CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
> CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
> SUM(Total_Inbound) AS TOTAL_QTY
> FROM dbo.tblGlobalInboundVolumes
> GROUP BY PART_ID, Region, Report_Year
> HAVING (PART_ID = 'KRC12110/3 R11F')|||Try:
SELECT
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
WHERE (PART_ID = 'KRC12110/3 R11F')
GROUP BY Region
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:457D9064-2B3B-4243-8648-E5884B22102E@.microsoft.com...
I have a table that contains a region, year, part, and quantity. I want the
query output to be one line per region with the years as columns for the sum
of the quantities. I've tried this, but I get one line per year instead of
one line per region.
SELECT
PART_ID,
REGION,
CASE Report_Year WHEN '2000' THEN SUM(Total_Inbound) ELSE 0 END AS "2000",
CASE Report_Year WHEN '2001' THEN SUM(Total_Inbound) ELSE 0 END AS "2001",
CASE Report_Year WHEN '2002' THEN SUM(Total_Inbound) ELSE 0 END AS "2002",
CASE Report_Year WHEN '2003' THEN SUM(Total_Inbound) ELSE 0 END AS "2003",
CASE Report_Year WHEN '2004' THEN SUM(Total_Inbound) ELSE 0 END AS "2004",
CASE Report_Year WHEN '2005' THEN SUM(Total_Inbound) ELSE 0 END AS "2005",
CASE Report_Year WHEN '2006' THEN SUM(Total_Inbound) ELSE 0 END AS "2006",
SUM(Total_Inbound) AS TOTAL_QTY
FROM dbo.tblGlobalInboundVolumes
GROUP BY PART_ID, Region, Report_Year
HAVING (PART_ID = 'KRC12110/3 R11F')

Grouping dissimilar data

I��m trying to build a report that contains bank account activity.For 3 customers their activity is this:

Deposits

DateAmount

John1/2/2007500.00

1/7/2007250.00

Mary1/3/2007100.00

Withdrawals

DateAmount

John1/3/2007100.00

Mary1/2/2007100.00

1/4/2007200.00

1/6/200750.00

Sam1/6/200750.00

I would like the report to have deposit/withdrawal info, with subtotals, grouped by customer, looking something like this:

Deposit Withdrawal

Cust DateAmountDateAmount

John 1/2/2007500.001/3/2007100.00

1/7/2007250.00

750.00 100.00

Mary1/3/2007100.001/2/2007100.00

1/4/2007200.00

1/6/2007 50.00

100.00 350.00

Sam 1/6/2007 50.00

0 50.00

Because the deposit and withdrawal data is only related by customer, I��ve broken it into 2 datasets.

I��m totally stumped!Can someone get me going in the right direction on this?

Thanks

Hi,

You can't put 2 datasets in one dataregion. To do this, I think it is best to do a Full Outer Join on the two sets based on Customer and Date. This way you can have records like:

CUST DEP_DT DEP_AMOUNT WIT_DATE WIT_AMOUNT
John 1-2-2007 500 NULL NULL
NULL NULL 1-3-2007 100
1-7-2007 250 NULL NULL

Hope this gets you started.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Grouping by datecolumn

Hi,

i have a table which contains the following columns:

Id = bigint id 1 +1
Machineid = bigint
logdate = datetime
vtp = bigint,

There are more records for 1 machine p/day
i want to group them together by complete day and get the sum of vtp for every different machine for each day.

Someone knows how to do this?
Thanx in front!
CHeers Wimselect machineid, convert(char(10),logdate,120), sum(vtp)
from <your_table>
where <if any conditions apply>
group by machineid, convert(char(10),logdate,120)
order by machineid, convert(char(10),logdate,120)

/*
look up cast / convert in BOL for other date / time formats
*/

Group Total..

I'm working on a Financial Report which contains a column "XYZ" , its value
is calculated from a formula by passing the row's record id and commission
rate. (the formula is inside a custom dll). The values are correctly
computed. Now, the footer should display the total of all the rows in the
group.
for instance:
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
Pepsi $361,000 gfyeefyefffee
Coca Cola $475,250 djfdfjdfddddd
RCola $28,757 re8reruejreerr
fdfsfnfsfssf
_________________________________________
Total: $ 865,007
Each of the "XYZ Total" in the above example, uses an expression as = FindTotal(recID!value, comm_rate!value)
In this case, how do I get the total in the footer? How to recursively add
the FindTotal expression when it contains the row's unique record id?
Thanks
P.S. The above data is a sample data. The actual report contains 3 different
levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
Transaction TitleI recently came across a new software, that I think you might want to look into.
www.simx.com/simx/home_report%20manager.htm
Works with SQL Server, and I was able to do reporting much like what you are describing.
"newmem" <"" wrote:
> I'm working on a Financial Report which contains a column "XYZ" , its value
> is calculated from a formula by passing the row's record id and commission
> rate. (the formula is inside a custom dll). The values are correctly
> computed. Now, the footer should display the total of all the rows in the
> group.
> for instance:
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> Pepsi $361,000 gfyeefyefffee
> Coca Cola $475,250 djfdfjdfddddd
> RCola $28,757 re8reruejreerr
> fdfsfnfsfssf
> _________________________________________
> Total: $ 865,007
> Each of the "XYZ Total" in the above example, uses an expression as => FindTotal(recID!value, comm_rate!value)
> In this case, how do I get the total in the footer? How to recursively add
> the FindTotal expression when it contains the row's unique record id?
> Thanks
> P.S. The above data is a sample data. The actual report contains 3 different
> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> Transaction Title
>
>|||While I appreciate your eagerness to help, I think that most people would
prefer that you refrain from advertising other products in a forum dedicated
to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
not to post there. :)
That being said, you should be able to define a custom field that does the
calculation and then referce the custom field in a sum in the group footer.
Presumably, you only need to add values from the inner group as the outer
group is just summary. If you want it to do parent / child hierarcy
aggregates, you need to use the recursive keyword.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>I recently came across a new software, that I think you might want to look
>into.
> www.simx.com/simx/home_report%20manager.htm
> Works with SQL Server, and I was able to do reporting much like what you
> are describing.
> "newmem" <"" wrote:
>> I'm working on a Financial Report which contains a column "XYZ" , its
>> value
>> is calculated from a formula by passing the row's record id and
>> commission
>> rate. (the formula is inside a custom dll). The values are correctly
>> computed. Now, the footer should display the total of all the rows in the
>> group.
>> for instance:
>> "Unit" "BrandName" "XYZ Total" "Comments"
>> Sodas
>> Pepsi $361,000 gfyeefyefffee
>> Coca Cola $475,250 djfdfjdfddddd
>> RCola $28,757 re8reruejreerr
>> fdfsfnfsfssf
>> _________________________________________
>> Total: $ 865,007
>> Each of the "XYZ Total" in the above example, uses an expression as =>> FindTotal(recID!value, comm_rate!value)
>> In this case, how do I get the total in the footer? How to recursively
>> add
>> the FindTotal expression when it contains the row's unique record id?
>> Thanks
>> P.S. The above data is a sample data. The actual report contains 3
>> different
>> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> Transaction Title
>>|||Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as => >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>

Group newbie: question on parsing a field value

I have a text field (called TreeNode) that contains node identifiers for a
dynamic tree on a web page. For example:

"1.1"
"1.2"
"1.2.1"
"1.2.2"
--
--
'1.2.10"
--
etc..

What I need to do is compare this field "value" to another value in a query.
(I'm using ASP and VBScript to create the statement). For example:

sql = "SELECT SomeField FROM MyTable WHERE TreeNode>=' " & MyNode & " ' "

The problem I run into is when the TreeNode value is say "1.2.10", and it's
being compared against "1.2.1" and "1.2.2". It should be greater than both
of these (in my implementation of this), but it actually falls between the
two, since it is a text comparison.

All I'm really interested in is the last value. But I can't use the RIGHT
function, because the last value may be one or more digits, and there could
be any number of levels (periods).

Is there an SQL function I could use that would strip away everything but
the text following the last period? I could then easily do the same in the
ASP script and compare integers. Something like:

sql = "SELECT SomeField FROM MyTable WHERE GetLastValueSQL(TreeNode) >= " &
GetLastValueASP(MyTreeNode)

This also needs to work in MS Access BTW :-)

Thanks in advance for any help!

Calan

AxMaster Guitar Software
www.jcsautomation.com
www.jcsautomation.com/music.asp
Music software and web design/hosting

"Reality exists only in the minds of the extremely deranged""Calan" <calan_svcREMOVE@.yaNOSPAMhoo.com> wrote in message news:<P3Jcc.643$FB1.182@.fe25.usenetserver.com>...
> I have a text field (called TreeNode) that contains node identifiers for a
> dynamic tree on a web page. For example:
> "1.1"
> "1.2"
> "1.2.1"
> "1.2.2"
> --
> --
> '1.2.10"
> --
> etc..
> What I need to do is compare this field "value" to another value in a query.
> (I'm using ASP and VBScript to create the statement). For example:
> sql = "SELECT SomeField FROM MyTable WHERE TreeNode>=' " & MyNode & " ' "
> The problem I run into is when the TreeNode value is say "1.2.10", and it's
> being compared against "1.2.1" and "1.2.2". It should be greater than both
> of these (in my implementation of this), but it actually falls between the
> two, since it is a text comparison.
> All I'm really interested in is the last value. But I can't use the RIGHT
> function, because the last value may be one or more digits, and there could
> be any number of levels (periods).
> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
> sql = "SELECT SomeField FROM MyTable WHERE GetLastValueSQL(TreeNode) >= " &
> GetLastValueASP(MyTreeNode)
> This also needs to work in MS Access BTW :-)
> Thanks in advance for any help!
> Calan
> AxMaster Guitar Software
> www.jcsautomation.com
> www.jcsautomation.com/music.asp
> Music software and web design/hosting
> "Reality exists only in the minds of the extremely deranged"

This is one way:

declare @.node varchar(50)
set @.node = '1.2.1.10'

select substring(
@.node,
len(@.node) - charindex('.', reverse(@.node))+2,
charindex('.', reverse(@.node))
)

Or this may be easier to read:

select reverse(left(reverse(@.node), charindex('.', reverse(@.node))-1))

You could put this into a function (in SQL2000), but it would be
invoked once per row in queries, so using a stored procedure is
probably a better approach.

Simon|||> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
> This also needs to work in MS Access BTW :-)

Hi,

I don't know anything about Access. You can use a table of numbers
trick to parse your "node" into individual nodes by using the period
as a delimiter. Erland has documented it in his site. In the code
below, I called my table of numbers TALLY which has one column ID with
values from 1,2,3,... to 8000.

declare @.node varchar (50)
set @.node='1.2.10'

SELECT
substring(phrase,s,(e-s-1)) as NODES
FROM
(
SELECT
id,
phrase,
charindex('.','.'+phrase+'.',id) as s,
charindex('.','.'+phrase+'.',id+1) as e
FROM tally,(select phrase=@.node) A
WHERE charindex('.','.'+phrase+'.',id) <
charindex('.','.'+phrase+'.',id+1)
) B

OUTPUTS:
NODES
----------------
1
2
10

Further modifying it to output the last NODE piece:
SELECT
substring(phrase,s,(e-s-1)) as NODES, identity(int,1,1) as i
INTO #T
FROM
(
SELECT
id,
phrase,
charindex('.','.'+phrase+'.',id) as s,
charindex('.','.'+phrase+'.',id+1) as e
FROM tally,(select phrase=@.node) A
WHERE charindex('.','.'+phrase+'.',id) <
charindex('.','.'+phrase+'.',id+1)
) B

SELECT NODES FROM #T WHERE i=(SELECT max(i) as i FROM #T)

OUTPUTS:
NODES
----------------
10

Group ID and ID Column

Hi,

I want to get the value from a row that contains the minimum value of a field AND for which I group by a 3rd field. For example:

The table tbl1 has:

X Quantity Location

1 20 slot 1

1 34 slot 1

3 17 slot 1

42 12 slot 5

5 65 slot 5

If I just want the quantities and location I can do:

Select Location, Min(Quantity) from tbl1 group by Location

However if I want X (or any other field in the table) I cannot do:

Select X, Location, Min(Quantity) from tbl1 group by Location

because X is not in the group by clause.

And putting X in the group by clause causes incorrect results.

Does anyone know the correct select statement?

jerry

It might help if you provided sample output from the query you are trying to write.|||

In the case of a group by, any non grouped columns in the select must be contained in an aggregate function.

Think about your example, If you group by Location which value of x should you get for location = 'slot 1' 1 or 3.

so, use MIN(X) or MAX(X) in your select and it should work

|||

Can you explain what you are trying to accomplish?

;with cte

as

(

select X, Quantity, Location, row_number() over(partition by Location order by Quantity) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

If you use sql server 2000,

Code Snippet

Create Table #qty (

[X] Varchar(100) ,

[Quantity] Varchar(100) ,

[Location] Varchar(100)

);

Insert Into #qty Values('1','20','slot1');

Insert Into #qty Values('1','34','slot1');

Insert Into #qty Values('3','17','slot1');

Insert Into #qty Values('42','12','slot5');

Insert Into #qty Values('5','65','slot5');

Select * From #qty [Main]

Join (

Select

[Location]

,Min([Quantity])[Quantity]

From

#qty

Group By

[Location]

) as [Data] On [Data].[Quantity] = [Main].[Quantity]

And [Data].[Location] = [Main].[Location]

GROUP BY, GROUP BY, and DISTINCT

I have an SQL-Server table that contains "date, name, and account number"
records like:
22-Jan-2005 Bill BA39833J
22-Jan-2005 Bill RJ3399K
22-Jan-2005 Bill KL9833LL
22-Jan-2005 Bill BA39833J
23-Jan-2005 Bill HP54599K
23-Jan-2005 Bill AA9833LL
23-Jan-2005 Bill BA90330Q
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill XX93939
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
I need to "group by" the date, and the user... and then "count()" the total
number
of account numbers that appear for each record.
... but here's the odd part...
I need to count each account # as "1"... except when there are similar
account numbers... then those all count as "1".
(Not counting each as "each".)
So the result will look something like:
22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count as
1)
23-Jan-2005 Bill 3
24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count as
1)
I thought all I would have to do is to "group by" all 3 fields... "date,
user, and account
number"... but that, of course, gives me something that I do NOT want.
ThanksTry,
select
colA,
colB,
count(distinct colC)
from
t
group by
colA,
colB
order by
colA,
colB
AMB
""A_Michigan_User"" wrote:

> I have an SQL-Server table that contains "date, name, and account number"
> records like:
> 22-Jan-2005 Bill BA39833J
> 22-Jan-2005 Bill RJ3399K
> 22-Jan-2005 Bill KL9833LL
> 22-Jan-2005 Bill BA39833J
> 23-Jan-2005 Bill HP54599K
> 23-Jan-2005 Bill AA9833LL
> 23-Jan-2005 Bill BA90330Q
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill XX93939
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> I need to "group by" the date, and the user... and then "count()" the tota
l
> number
> of account numbers that appear for each record.
> ... but here's the odd part...
> I need to count each account # as "1"... except when there are similar
> account numbers... then those all count as "1".
> (Not counting each as "each".)
> So the result will look something like:
> 22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count
as
> 1)
> 23-Jan-2005 Bill 3
> 24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count
as
> 1)
> I thought all I would have to do is to "group by" all 3 fields... "date,
> user, and account
> number"... but that, of course, gives me something that I do NOT want.
> Thanks
>
>