Count distinct and non distinct values

Question

I have an sql table looking like this:

+----+-----------+------------+
| Id | EnquiryId | PropertyId |
+----+-----------+------------+
|  1 |         1 |        20  |
|  2 |         1 |        25  |
|  3 |         1 |        26  |
|  4 |         2 |        20  |
|  5 |         3 |        20  |
|  6 |         4 |        20  |
+----+-----------+------------+

I want to count how many enquiries propertyid 20 has on it's own, and how many that is shared with other properties

So the result should be something like this:

Number of single enguiry: 3

Number of shared enquiries: 1

It's perfectly fine if it requires two select statements :)

The attempts so far looks like this:

(select count(distinct [EnquiryId]) 
from [EnquiryProperty] where propertyid=20) as 'SingleEnquiry'

This gave me 4 results (I need it to be 3)

(select count([PropertyId]) FROM [EnquiryProperty] where propertyid=20 GROUP BY propertyid HAVING COUNT(*) > 1) as 'MultipleEnquiry'

And this gave me 0 results

https://stackoverflow.com/questions/13146304/how-to-select-every-row-where-column-value-is-not-distinct @marcus-ohlsson — Andy K, Oct 19 '17 at 10:17
Ahem : https://stackoverflow.com/questions/1521605/sql-server-query-selecting-count-with-distinct — NWS, Oct 19 '17 at 10:17
My attemts so far looks like this: ,(select count(distinct [EnquiryId]) from [EnquiryProperty] where propertyid=325) as 'SingleEnquiry' ,(select count([PropertyId]) FROM [EnquiryProperty] where propertyid=325 GROUP BY propertyid HAVING COUNT(*) > 1) as 'MultipleEnquiry' — Marcus Ohlsson, Oct 19 '17 at 10:19
@MarcusOhlsson:Is it sqlserver/oracle/mysql..also please share your expeceted result — TheGameiswar, Oct 19 '17 at 10:23
A database cannot be SQL since SQL is the language in which you query the database. — DanteTheSmith, Oct 19 '17 at 10:25
@TheGameiswar: It's an sqlserver and the resultI need is 3 and 1 — Marcus Ohlsson, Oct 19 '17 at 10:25
@DanteTheSmith You are right, I missunderstood the question from L30n1d45 — Marcus Ohlsson, Oct 19 '17 at 10:26
@MarcusOhlsson:Glad you found it now..Stackoverflow is so helpfull,if you can follow a good format in asking questions.. you can check out this link to know more on how to ask a good question:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Oct 19 '17 at 10:29

score 5 · Accepted Answer · edited Oct 19 '17 at 10:27

5

One method is two levels of aggregation. The inner level assigns flags to each enquiry. The second uses those to get the information you want:

select sum(is_20 * (1 - is_not_20)) as single_enquiry,
       sum(is_20 * is_not_20) as shared_enquiry
from (select enquiryid,
             max(case when propertyid = 20 then 1 else 0 end) as is_20,
             max(case when propertyid <> 20 then 1 else 0 end) as is_not_20
      from t
      group by enquiryid
     ) e;

edited Oct 19 '17 at 10:27

Srini V

11,045
14
66
89

answered Oct 19 '17 at 10:25

Gordon Linoff

1,242,037
58
646
786

Thank you verry much @SriniV, this works like a sharm :) – Marcus Ohlsson Oct 19 '17 at 10:32
I like the idea with flagging 1st then aggregating the flags. Gonna keep that one in memory :) – DanteTheSmith Oct 19 '17 at 10:35
@MarcusOhlsson, it was actually gordonlinoff to be thanked – Srini V Oct 19 '17 at 10:56

score 1 · Answer 2 · answered Oct 19 '17 at 10:34

You can do same in 2 steps (its a tad easier to understand and has no nesting):

select count(PropertyId) AS Single20s
from WhateverTableIsCalled
group by EnguryId
having count(PropertyId) = 1

select count(PropertyId) AS Shared20s
from WhateverTableIsCalled
group by EnguryId
having count(PropertyId) > 1

Count distinct and non distinct values

2 Answers2