How to get unique: Apartment or House or Both in a SQL Query?

I have a table like below, every line have 2 options, Apartment or Houseor or Both!

I can get unique for Apartment and House, but not for Both also

How can I get all 3 in one SQL Query, 3 unique sums?


My table with data
My table with data
Hocke_swedenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Please detail what output you are expecting.
0
Scott PletcherSenior DBACommented:
As below.  You may need to change '' (empty) to NULL, depending on the exact data in your table:

SELECT
--...,
SUM(CASE WHEN Apartment > '' AND House = '' THEN 1 ELSE 0 END) AS Apartment_Unique,
SUM(CASE WHEN Apartment = '' AND House > '' THEN 1 ELSE 0 END) AS House_Unique,
SUM(CASE WHEN Apartment > '' AND House > '' THEN 1 ELSE 0 END) AS Both_Unique
FROM dbo.tablename
...
0
Hocke_swedenAuthor Commented:
Here is a better example, with data and everything


The sample give me
3 counted apartment=0
5 counted  apartment=1

But both is missing
1 counted apartment=0 and 1

How to get out Both?



SQL Query
select apartment, count(distinct data) as counted from Apartment_Table
  group by apartment

Open in new window


Table
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Apartment_Table](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[data] [int] NULL,
	[apartment] [bit] NULL,
 CONSTRAINT [PK_Apartmen] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window



Data
insert into apartment_table (apartment, data) values(0,820010)
insert into apartment_table (apartment, data) values(0,820010)
insert into apartment_table (apartment, data) values(0,820010)
insert into apartment_table (apartment, data) values(1,820024)
insert into apartment_table (apartment, data) values(0,820050)
insert into apartment_table (apartment, data) values(1,820056)
insert into apartment_table (apartment, data) values(1,820056)
insert into apartment_table (apartment, data) values(1,820082)
insert into apartment_table (apartment, data) values(1,820086)
insert into apartment_table (apartment, data) values(1,3003097)
insert into apartment_table (apartment, data) values(0,3003097)
insert into apartment_table (apartment, data) values(1,3003097)

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark WillsTopic AdvisorCommented:
Well, it is a bit more involved because you need to fist check to see if any one data has both an apartment and a house.

So, you have to first group by the data

Then do the sums, except you don't really care is "data" appears once or five times (that distinct count tells us that). In which case we wont sum() but we will use max().

So it is not enough to do count(distinct data). You first have to group by data and then decide if any one data has both.

either as a subquery, or as a CTE e.g.

;with cte_data as
( select data
      ,max(case when apartment = 0 then 1 else 0 end) as house
      ,max(case when apartment = 1 then 1 else 0 end) as apartment
  from Apartment_Table
  group by data
) 

select sum(case when house > 0 and apartment = 0 then house else 0 end) as house
      ,sum(case when house = 0 and apartment > 0 then apartment else 0 end) as apartment
      ,sum(case when house > 0 and apartment > 0 then 1 else 0 end) as both
from cte_data

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
If you wanted to continue with your current approach, then you have the option of using a JOIN (but still have to do the maths, and then format horizontally)

as a guide...

select a.apartment, count (distinct a.data), count(distinct h.data) 
from Apartment_Table a
left outer join Apartment_Table h on a.data = h.data and a.apartment <> h.apartment
group by a.apartment 

Open in new window

0
hnasrCommented:
This is your input data, list the required output

id      data      apartment
1      820010      0
2      820010      0
3      820010      0
4      820024      1
5      820050      0
6      820056      1
7      820056      1
8      820082      1
9      820086      1
10      3003097      1
11      3003097      0
12      3003097      1
0
Hocke_swedenAuthor Commented:
Execellent solution, Thanks! It was smart!
0
Olaf DoschkeSoftware DeveloperCommented:
Too late, still I would do it this way:

Create Table #estate (house int NULL, apartment int NULL)

Insert into #estate 
Select 1,1
union 
Select 2, NULL
union
Select 3, NULL
union
Select NULL,4 

Select 
count(house)-count(house+apartment)     as onlyhouses, 
count(apartment)-count(house+apartment) as onlyapartments, 
count(house+apartment)                  as both 
From #estate

Drop Table #estate

Open in new window


This makes use of NULL and behaviour of Count() and the + operator with NULLs.

1. Count(expr) only counts records, where expr is NOT NULL
2. a+b is NULL, if either a or b is NULL (the same goes for * or - or many other operators)
Of course
3. The number of only houses is the number of houses - number of both. Same goes for apartments.

There you go. No need for CASEs, SUMs etc.

Bye, Olaf.
0
Mark WillsTopic AdvisorCommented:
@Hocke_sweden happy to help :)

@Olaf, interesting approach, but there is only one "bit" field to ascertain house or apartment, so your solution wont work in this case.

And as a general comment, it is always best to handle NULLS because it can throw warnings depending on your settings (ansi_warnings and arithabort) which can compromise a script or batch from running successfully.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.