Solved

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

Posted on 2013-10-17
Medium Priority
171 Views
Last Modified: 2013-11-18
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
0
Question by:Hocke_sweden
9 Comments

LVL 34

Expert Comment

ID: 39581255
Please detail what output you are expecting.
0

LVL 70

Expert Comment

ID: 39581387
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

Author Comment

ID: 39641476
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
``````

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
``````

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)
``````
0

LVL 52

Accepted Solution

Mark Wills earned 2000 total points
ID: 39657229
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
``````
0

LVL 52

Expert Comment

ID: 39657274
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
``````
0

LVL 31

Expert Comment

ID: 39657305
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

Author Closing Comment

ID: 39657580
Execellent solution, Thanks! It was smart!
0

LVL 30

Expert Comment

ID: 39657688
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
``````

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

LVL 52

Expert Comment

ID: 39658279
@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

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month11 days, 15 hours left to enroll

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.