Counting unique entries in a SQL Statement

I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.

I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".

Does this make sense?

EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....

SAGMIR 025 001 HEM/FIR HLF550...

SAGMIR 025 001 HEM/FIR KILN-D HLF505...

SAGMIR 025 002 HEM/FIR HLF660....

The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...

Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!

Michael

Yes, this is not clear to me yet. Could you explain this a bit in detail ? There is a function in SQL Server which can produce a rank based on several columns. The example for procuding such number would be to get new running number for Order Details per order number. Is is this what you are trying to do ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Your best bet is probably a temporary table... and then join back to it.something like the following :

create table #tempinv
(
invid int identity(1,1),
bl_id int not null
)

insert into #tempinv
(
bl_id
)
select distinct BL_ID from invoices
order by BL_ID

select i.VES, i.VOY, t.invid, i.PRO, i.MARK
from invoices i
inner join #tempinv t on t.bl_id = i.BL_ID
order by i.BL_ID

drop table #tempinv

I think this is what you want....

Counting Results in a field in SQL 2000

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Quote:

Originally Posted by Alpenk

I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?

Thanks

Please send an example of your table or tables

|||select count(column_name) from table_name

In this case you count all not null values in this column.
If you want to count distinct values in this colun do following:

Select count(distinct column_name) from table_name

Good Luck.

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle
my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of custome
rs and number of different resources.
When the end user is creating a pivot table with Region en time on the axis,
the count of resources should reflect on the crossings. And I really don't
know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please helpF.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/d...r />
inct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tack
le my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of custo
mers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axi
s, the count of resources should reflect on the crossings. And I really don'
t know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

Counting members (with calculated members?)

Hi,
I'm building a cube to analyze Job entries, using Targit as a front-end.
After very disappointing hours of trying hopefully one of you know to tackle my problem.
My fact table (simplified):
Date, Job#, Customer#, Resource#, Hours Spent
And some linked tables for CustomerName/Group, ResouceName
Dimensions are:
Job#
Region, Customer
Customer
Time (Y-Q-M-D)
Summing the quantity of hours spent can easily be done by a measure.
BUT now my problem:
I would like to have a count of the different jobs and the number of customers and number of different resources.
When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
E.G.
Date, job, resource, customer, hours
04-02-13, 1, A, CustX, 8
04-02-14, 1, B, CustX, 8
05-01-01, 1, C, CustZ, 8
Pivot on Year And Resource with count of resources should give
A B C
2004 1 1 0
2005 0 0 1
All 1 1 1
While a pivot on Year and Customer
CustX CustZ
2004 2 0
2005 0 1
All 2 1
Please help
F.Y.I.
Take a look at Analysis Services: DISTINCT COUNT, Basket Analysis, and
Solving the Multiple Selection of Members Problem
http://msdn.microsoft.com/library/de.../distinct2.asp
"Marcel" wrote:

> Hi,
> I'm building a cube to analyze Job entries, using Targit as a front-end.
> After very disappointing hours of trying hopefully one of you know to tackle my problem.
> My fact table (simplified):
> Date, Job#, Customer#, Resource#, Hours Spent
> And some linked tables for CustomerName/Group, ResouceName
> Dimensions are:
> Job#
> Region, Customer
> Customer
> Time (Y-Q-M-D)
> Summing the quantity of hours spent can easily be done by a measure.
> BUT now my problem:
> I would like to have a count of the different jobs and the number of customers and number of different resources.
> When the end user is creating a pivot table with Region en time on the axis, the count of resources should reflect on the crossings. And I really don't know how to create the correct MDX for the calculated member in the cube.
> E.G.
> Date, job, resource, customer, hours
> 04-02-13, 1, A, CustX, 8
> 04-02-14, 1, B, CustX, 8
> 05-01-01, 1, C, CustZ, 8
> Pivot on Year And Resource with count of resources should give
> A B C
> 2004 1 1 0
> 2005 0 0 1
> All 1 1 1
> While a pivot on Year and Customer
> CustX CustZ
> 2004 2 0
> 2005 0 1
> All 2 1
> Please help
>

Counting Consecutive days in SQL

I've been trying to figure out how to count the number of consecutive
days of entries in a table. the table stores data on number of minutes
exercised in a day for users of an exercise program:
ExerciseId int
UserId int
Minutes int
ExerciseDate datetime
I need to produce a list of users who exercised XX number of days in a
row. e.g. 60 consecutive days of exercise. Any ideas on how to do that?
I'm using SQL Server 2000.
ThanksVibroluxOn10@.gmail.com wrote:
> I've been trying to figure out how to count the number of consecutive
> days of entries in a table. the table stores data on number of minutes
> exercised in a day for users of an exercise program:
> ExerciseId int
> UserId int
> Minutes int
> ExerciseDate datetime
> I need to produce a list of users who exercised XX number of days in a
> row. e.g. 60 consecutive days of exercise. Any ideas on how to do that?
> I'm using SQL Server 2000.
> Thanks
>
http://www.aspfaq.com/show.asp?id=2519