distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

Distinct on single column?

Hi,

This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.

SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d

INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p ON p.code = sol.part

WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0

ORDER BY (p.code), datum DESC

The problem with this is that it returns multiple lines for every product (p.code). Like so:

code description price quantity total date

603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00

603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00

603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00

603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00

603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00

603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00

603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00

I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved

edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.

Hello

Just a few ideas, do not have an MSSQL instace nearby to test:

1) use cursor, which I'd prefer to avoid

- declare cursor for "Select Distinct (code) From EfasLive..part"

- for each cursor value do the select on joined tables where date = MAx(date) to get the most recent value

2) do something like

Select ....
From (Select Distinct (code) From EfasLive..part) as p Inner Join... (the rest of the tables)...
Where date = Max(date)

The idea is to join distinct "code" values with other tables and filter only the most recent one for each table (that's what "Max(date)" is for)

3) try to use CTEs (Common Table Expressions)

Post the solution after you find one! Tnx
|||

Hmz I'll try out some of this stuff. Thx! But the multiple instances of code don't come from Efaslive..part. They are actually from so_line. An so_line is actually an orderline. For every order there could be multiple lines each containing a different product. But since it's over a timespan of 3 months it will include multiple orders and thus multiple so_lines containing the same product (once for every order it was in). So doing a distinct on code in Efaslive..part probably won't work. Or at least it doesn't make sense to me I'll most definitely look into CTEs and post my findings or a solution.

edit: actually this can be simplified ... just pretend that the result I get is a simple select query from a single table. As if it was a CTE Even then I'd have no clue how to drop the older records The only technique I know is to group them but then you'd have to use MAX or COUNT or AVG or whatever .. and then I wouldn't have the correct price and/or date. So you wouldn't realy be dropping them.

I'll look into the pointer thing.

|||

Here the query,

Code Snippet

;With CTE

as

(

SELECT DISTINCT

p.code

, p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description

, sol.p_sales as price

, sol.q_ordered as quantity

, sol.p_sales * sol.q_ordered as total

, so.date_in as date

, max(so.date_in) over(partition by p.code) as maxdate

--, Row_Number() over(partition by p.code order by so.date_in desc) rid

FROM

EfasLive..debtor AS d

INNER JOIN Informatica..so AS so

ON so.deb_code = d.code

AND so.co_code = d.co_code

INNER JOIN Informatica..so_line AS sol

ON sol.code = so.code

AND sol.co_code = so.co_code

AND sol.acc_year = so.acc_year

AND sol.efas = so.efas

INNER JOIN EfasLive..part AS p

ON p.code = sol.part

WHERE

d.[grp{003}] = 'GROUP'

AND p.co_code = 1

AND p.code NOT LIKE '&%'

AND so.date_in > DATEADD(m,-3,GETDATE())

AND sol.q_ordered > 0

)

Select

code

, description

, price

, quantity

, total

, date

From

CTE

Where

date = maxdate

--rid=1

DISTINCT on a single column

Hi,

I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

Example:

Name......ID

John.......1
John.......2
Mary.......3

I need only one record for John. But both Name and ID should be selected.

Thanks"I need only one record for John. But both Name and ID should be selected."

What value do you expect to be present in the ID field, given your example data?|||Any value in the ID field can be selected. It's only the name that matters.|||???

So what result to you want from:

John.......1
John.......2
Mary.......3

Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

Option C:
John, 1
, 2
Mary, 3

Your logic is not clear.

blindman|||Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

Thanks|||Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ?|||Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

Try using Limit or TOP|||You want a group by query.

Select Name, Min(ID)
from YourTable
Group By Name

blindman|||What's "Limit"?

blindman|||Here is the acual data

Name......ID...Dept..University...

John......17...A........XYZ
John......18...B........XYZ

Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

Thanks|||i think "limit" is in mysql and not in mssql ... wrong forum buddy|||Assuming ID is a unique value in your table (it better be, or your logic is not possible):

select YourTable.*
from YourTable
inner join
(select Name,
Min(ID) ID
from YourTable) DistinctIDs
on YourTable.ID = DistinctIDs.ID

This selects a single ID for each unique name and returns the data associated with that ID.

blindman|||"select Name,
Min(ID) ID
from YourTable"

Doesn't a Group By clause need to be here.|||Yeah, that would probably help... :rolleyes:

blindman

Distinct list

Let’s say we have two tables with the following values:
Table p1: Product Table p2: Product
-- --
A B
B C
C D
C D
Task #1 - In a single query, generate a unique list of values from both
tables (i.e. A, B, C, D).Hi Danlin,
Select Distinct Product From P1
Union
Select Distinct Product From P2
<Throw in an order by here if you want>
Order By
Product
That should do it.
Richard|||SELECT product
FROM P1
UNION
SELECT product
FROM P2
David Portas
SQL Server MVP
--|||Someone throw me this problem and he wants a single query solution not using
union. Sorry about the confusion. Thanks.
"danlin" wrote:

> Let’s say we have two tables with the following values:
>
> Table p1: Product Table p2: Product
> -- --
> A B
> B C
> C D
> C D
>
> Task #1 - In a single query, generate a unique list of values from both
> tables (i.e. A, B, C, D).
>|||SELECT DISTINCT
COALESCE(P1.product, P2.product)
FROM P1
FULL JOIN P2
ON P1.product = P2.product
--
David Portas
SQL Server MVP
--|||Thanks, you're graet David.
"David Portas" wrote:

> SELECT DISTINCT
> COALESCE(P1.product, P2.product)
> FROM P1
> FULL JOIN P2
> ON P1.product = P2.product
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Dissociation between DTS and Scheduled Job

Hi,

I've got a DTS package with a single step that runs an exe via Process Task. The DTS runs fine when I execute it manually, but when I schedule the package, the job says it runs successfully, but does not execute the package. Now, a few facts:

The DTS is owned by user1
The Job is owned by user1
All security credentials match up
The exe is on a network with full unc path specified
SQLAgent service is running in user1's context
All user input is on the actual SQL server, but exe is on network


When I change the DTS from running the exe to performing a simple query, the job executes the DTS just fine. When I move the exe from the network and place it locally on the server, the job still won't execute the DTS. What am I missing here? This is driving me crazy!!Shameless newbie bump...|||View the job history. Click the checkbox to show detail. Is there anything interesting there?

-PatP|||Hi Pat,

The job history tells me that all scheduled runs were successes, that the last step to run was step 1 (the only step), but the run time was one or two seconds. This one step that runs, however, should take between 2 and 3 minutes to complete (and does when DTS is executed manually).

That's all I get from the job history...

TIA|||SQLAgent service is running in user1's context
All user input is on the actual SQL server, but exe is on network

How is that possible?

In all my experience when a DTS package runs when manually executed, it's running under the context of the client, when scheduled, it runs under the context of the server...

Drive mappings and Security have always been the reason as to why the scheduled job fails...

Another reason why I stopped using DTS a long time agao for ANY production processes...

Displaying same column name in the same column

Hi, I'm trying to accomplish something with this code:

SELECT ProductionOrder.PO, ProductionOrder.Part, Single.Length,

Single.Date, Pairs.Length, Pairs.Date FROM [ProductionOrder]

LEFT OUTER JOIN Pairs ON Pairs.PO = [ProductionOrder].PO AND

Pairs.Part = [ProductionOrder].Part

LEFT OUTER JOIN Single ON Single.PO = [ProductionOrder].PO AND

Single.Part = [ProductionOrder].Part

WHERE (Single.Broke='True' AND (Single.[BrokeFixed] = 'False' OR Single.[BrokeFixed] IS NULL))

OR (Pairs.Broke='True' AND (Pairs.[BrokeFixed] = 'False' OR Pairs.[BrokeFixed] IS NULL))

With this I get the following result:

PO | Part | Length | Date | Length | Date

-

602520 | 3 | 24000 | 2007-08-24 15:33:33.727 | NULL | NULL
602521 | 3 | NULL | NULL | 14550 | 2007-08-29 17:41:01.930

But what I want is:

PO | Part | Length | Date

602520 | 3 | 24000 | 2007-08-24 15:33:33.727
602521 | 3 | 14550 | 2007-08-29 17:41:01.930

How can I accomplish this? Please, any help would be very much appreciated.

Thanks a lot in advance

You can use ISNULL or COALESCE.

eg SELECT ISNULL(Single.Date, Pairs.Date) AS Date, ISNULL(Single.Length, Pairs.Length) AS length

Remember, this method will always use Single.Date unless its NULL so if both are populated, then Single.Date will be displayed. This also won't check a dependancy on the Date and Length ie its possible that you could have a Single.Date with a Pairs.Length unless you are 100% sure this situation could never occur. If it might, you may want to consider using a CASE statement to choose which values to display.

Check CASE/ISNULL/COALESCE out in more detail in Books Online.

HTH!

|||

Thanks a lot! That's excatly what I needed. I'm not an expert T-SQL programmer so I was getting a hard time trying

to get this result. And no, they will never be both populated as there will never be the same PO number on both

Single and Pairs table. But both PO numbers will be on the ProductionOrder Table as all POs (ProductionOrders)

will be on the ProductionOrder table, but each PO has a corresponding table, defining its type with its details (Single, Pairs, Groups, and FinalProducts).

Thanks again. Both of you who tried to help me out.

Regards

Fábio

displaying records in two tables

HI,
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat

displaying multiple charts using a single dataset

Hello,

I have the following tables :

Area
areano
areaname

Locality

locno
areano

RequestType
reqid
reqdtls

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.

I have written the query for this.

select q.[areaname] as Area,

classCount as TotalCount, ReqType

from(select a.areano,

a.classCount, ReqType

from(select areano,

count(*)as classCount, reqdtls as ReqType

from WebSummit

join RequestType

on WebSummit.reqid = RequestType.reqid

where date_created >=dateadd(mm,-3,getdate())

groupby areano, reqdtls

) a

join WebSummit b

on a.areano = b.areano

groupby a.areano,

a.classCount, ReqType

) p

join Area q

on p.areano = q.areano

The query works fine and has been tested. Now I want to display this in chart format.

The display should be in a chart format, one for each area showing a bar for each RequestType.

I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.

I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.

Plz help me out.

Here's some sample data

Area

AreaNo AreaName

1 Area1

2 Area2

3 Area 3

4 Area4

5 Area5

6 Area6

7 Area7

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 1

4 Ben 12/6/2007 3 1

5 Dale 14/6/2007 1 2

6 Evjen 15/6/2007 1 2

7 Fuller 16/6/2007 2 2

8 Jimmy 16/6/2007 3 2

9 Kart 16/6/2007 3 2

10 Fuller 16/6/2007 3 2

Regards,

Vidya

Any one has a clue?

Display details section for each group

Hi,

I want to display details ection for each group in a single table using one dataset.

For ex:

Group 1 Row --> Name Age

Details 1 Row--> XXX 30

Details 2 Row--> YYY 20

Group 2 Row --> City Country

Details 1 Row--> CCC ZZZZ

Details 2 Row--> BBB AAAA

Hi,

Read this article in BOL please: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/a4aa29f9-eaa4-4116-8e22-87748c5ef0b8.htm

It is much more easier tham I write here

Regards,

Janos

|||

Hi Janos,

Thanks for the reply. But i am not able to access the url given by you. Can you please provide me the correct URL?

Thanks in Advance.,

Parimal

|||

Hi,

it is in the books online for SQL server 2005. Try to copy/paste into it, or open it on the MS website at here: http://technet.microsoft.com/en-us/library/ms159169.aspx

Regards,

Janos