Preceding Distinct Load and Count

[Anonymous]

I am trying to do a distinct load with a count in a preceding load like as follows:

[Script Count]:

Load ScriptNumber,

Count (ScriptNumber) as RX;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

This gives an invalid expression error.  Kindly assist

[sunny_talwar · Accepted solution]

You would need a Group By Statement any time you do Aggregation within a load having other fields outside the aggregation.

Try this:

[Script Count]:

Load ScriptNumber,

Count (ScriptNumber) as RX

Group By ScriptNumber;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

or may be this if you don't need overall count (rather than count per ScriptNumber)

[Script Count]:

Load Count (ScriptNumber) as RX;

SQL SELECT DISTINCT ScriptNumber

FROM Mysever.dbo.MyDatabase;

[Anonymous]

Thanks Suny let me try it out and will dvise accordingly

[maxgro]

why not at the db side?

SELECT COUNT(DISTINCT ScriptNumber) AS RX

FROM Mysever.dbo.MyDatabase;

but there is a think I don't understand

If you do a distinct ScriptNumber on the db,

you can only get distinct values

1

2

3

4

...

1000

and the count is always 1

[ElizaF]

Try this:

[Script Count]:

Load

ScriptNumber,

RX;

SQL SELECT DISTINCT ScriptNumber, Count (ScriptNumber) as RX

FROM Mysever.dbo.MyDatabase

Group by ScriptNumber ;

[Anonymous]

Hi Sunny

Many thanks, the first option is what i was looking for. Can you explain a little bit more what is exactly taking place.

[Anonymous]

Many thanks, as you said, if i do it at the data base side i will lose some valuable data.

[Anonymous]

Thanks Eliza

I will also try this out