Count vs Distinct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ivan Debono - 18 Apr 2006 14:29 GMT

Hi all,

I need to create a query based on a master-details tables scenario, where a
record of the master table can have multiple details records.

For example:
Master Table has 3 records with just 1 field called id (values 1, 2 and 3)

Details table is like this
master_id id
1 10
2 10
2 20
2 30
3 20
3 20

I want to find out because master records have more than 1 detail record and
those detail records are the same.

From the above details table, master id 1 is not included because it has
just 1 details record. master id 2 has 3 details records but their ids are
different. Only master id 3 should be returned.

Thanks,
Ivan

Reply to this Message

[MVP] S.Clark - 18 Apr 2006 15:14 GMT

Either
Select master_id, count(id) from details GROUP BY master_id having count(id)

> 1

or
Select master_id, count(id) from details GROUP BY master_id where count(id)

> 1

One of those should get you close.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> Hi all,
>
[quoted text clipped - 24 lines]
> Thanks,
> Ivan

Reply to this Message

KARL DEWEY - 18 Apr 2006 15:16 GMT

Try this --
SELECT MasterID.master_ID, MasterID.ID
FROM MasterID
GROUP BY MasterID.master_ID, MasterID.ID
HAVING (((Count(MasterID.ID))>1));

> Hi all,
>
[quoted text clipped - 22 lines]
> Thanks,
> Ivan

Reply to this Message

Ivan Debono - 18 Apr 2006 16:16 GMT

That gives me all master records that have more than 1 details record. How
can I expand the statement to get all those master records that have all
detail id's the same?

Ivan

> Try this --
> SELECT MasterID.master_ID, MasterID.ID
[quoted text clipped - 28 lines]
> > Thanks,
> > Ivan

Reply to this Message

KARL DEWEY - 18 Apr 2006 17:38 GMT

This does give you all the master records that have multiple detail id's the
same.

> That gives me all master records that have more than 1 details record. How
> can I expand the statement to get all those master records that have all
[quoted text clipped - 38 lines]
> > > Thanks,
> > > Ivan

Reply to this Message

Michel Walsh - 18 Apr 2006 19:48 GMT

Hi,

SELECT *
FROM masterID
WHERE master_ID IN( SELECT master_ID
FROM masterID
GROUP BY master_ID, id
HAVING COUNT(*) >1)

is a possible solution.

Hoping it may help,
Vanderghast, Access MVP

> This does give you all the master records that have multiple detail id's
> the
[quoted text clipped - 47 lines]
>> > > Thanks,
>> > > Ivan

Reply to this Message

Ivan Debono - 19 Apr 2006 12:18 GMT

It's half the way though!!

It does return all master records that have more than 1 details record. But
I need all master records where count(details) > 1 AND distinct(details)=1
!!

This means that all the id's of the details for a particular master record
are all the same.

Ivan

> This does give you all the master records that have multiple detail id's the
> same.
[quoted text clipped - 41 lines]
> > > > Thanks,
> > > > Ivan

Reply to this Message

John Spencer - 19 Apr 2006 12:57 GMT

This may work for you

SELECT Master.Master_ID
FROM Master INNER JOIN Details
ON Master.Master_ID = Details.Master_ID
WHERE Master.Master_ID IN
(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
HAVING Count(D.MasterID) = 1)
GROUP BY Master.Master_ID
HAVING Count(Master.Master_ID) > 1

> It's half the way though!!
>
[quoted text clipped - 64 lines]
>> > > > Thanks,
>> > > > Ivan

Reply to this Message

Ivan Debono - 19 Apr 2006 13:43 GMT

This part:

(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
HAVING Count(D.MasterID) = 1)

Gives an error that one of the columns is not part of an aggregate function.

Ivan

> This may work for you
>
[quoted text clipped - 78 lines]
> >> > > > Thanks,
> >> > > > Ivan

Reply to this Message

John Spencer - 19 Apr 2006 16:04 GMT

I think this will fix that.

SELECT Master.Master_ID
FROM Master INNER JOIN Details
ON Master.Master_ID = Details.Master_ID
WHERE Master.Master_ID IN
(SELECT D.Master_ID
FROM
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
GROUP BY D.Master_ID
HAVING Count(D.Master_ID) = 1)
GROUP BY Master.Master_ID
HAVING Count(Master.Master_ID) > 1

> This part:
>
[quoted text clipped - 104 lines]
>> >> > > > Thanks,
>> >> > > > Ivan

Reply to this Message

Ivan Debono - 19 Apr 2006 13:47 GMT

I guess we can eliminate the master table complete and concentrate on the
details table only. Imagine the details table is an order_details table,
with columns:
id (autonumber)
order_id
product_id

The query should return all orders where count(product_id)>1 and distinct
count(product_id) = 1.

Ivan

> This may work for you
>
[quoted text clipped - 78 lines]
> >> > > > Thanks,
> >> > > > Ivan

Reply to this Message

John Spencer - 19 Apr 2006 18:52 GMT

QueryA:
SELECT Distinct Order_ID, ProductID
FROM Order_Details

QueryB:
SELECT Order_ID, Count(Product_ID) as CountProducts
FROM QueryA
GROUP BY Order_ID
HAVING Count(Product_ID) = 1

QueryC:
SELECT Order_Details.Order_ID
FROM QueryB INNER JOIN Order_Details
ON QueryB.Order_ID = Order_Details.Order_ID
Group By Order_Details.Order_ID
Having Count(Order_Details.Order_ID) > 1

>I guess we can eliminate the master table complete and concentrate on the
> details table only. Imagine the details table is an order_details table,
[quoted text clipped - 103 lines]
>> >> > > > Thanks,
>> >> > > > Ivan

Reply to this Message

Ivan Debono - 20 Apr 2006 07:52 GMT

You beat me to it for a few minutes!!! This is what I was trying to do. It
works!

Thanks,
Ivan

> QueryA:
> SELECT Distinct Order_ID, ProductID
[quoted text clipped - 120 lines]
> >> >> > > > Thanks,
> >> >> > > > Ivan

Reply to this Message

John Spencer - 18 Apr 2006 18:08 GMT

THe following might work for you.

SELECT M.Master_ID, Count(D.ID) as UniqueValues
FROM Master_Table as M INNER JOIN
(SELECT DISTINCT Details.Master_ID, Details.ID
FROM Details) as D
ON M.MasterID = D.Master_ID
GROUP BY M.MasterID, D.ID
HAVING Count(D.ID) > 1

If that doesn't work. Build a query getting the distinct values of MasterID
and DetailID on the details table and then join it the master table and do a
count there.

> Hi all,
>
[quoted text clipped - 24 lines]
> Thanks,
> Ivan

Reply to this Message