CODEWITHSUNDEEP
sqlsql-server
user1929393
user1929393

Reputation: 4267

SQL Joining Query How To

CREATE TABLE interview (uniqueID int identity(1,1), 
                        date datetime, 
                        recordtype int, 
                        amount numeric(18, 4))

INSERT INTO interview values('6/30/13', 1, 27.95)
INSERT INTO interview values('5/20/13', 1, 21.85)
INSERT INTO interview values('5/22/13', 2, 27.90)
INSERT INTO interview values('12/11/12', 2, 23.95)
INSERT INTO interview values('6/13/13', 3, 24.90)
INSERT INTO interview values('6/30/13', 2, 27.95)
INSERT INTO interview values('5/20/13', 2, 21.85)
INSERT INTO interview values('5/22/13', 1, 27.90)
INSERT INTO interview values('12/11/12',1, 23.95)
INSERT INTO interview values('6/13/13', 3, 24.90)
INSERT INTO interview values('6/30/13', 3, 27.95)
INSERT INTO interview values('5/20/13', 3, 21.85)
INSERT INTO interview values('5/22/13', 2, 27.90)
INSERT INTO interview values('12/11/12', 1, 23.95)
INSERT INTO interview values('6/13/13', 1, 24.90)

How to get the following result? What would the query look like?

Query Result

I was only able to get a partial to work, but my answer is not correct. I need to somehow join the queries. 

select distinct date, count(RecordType)as Count_unique1
from interview
where RecordType = '1'
group by date

select distinct date, count(RecordType)as Count_unique2
from interview
where RecordType = '2'
group by date

select distinct date, count(RecordType)as Count_unique3
from interview
where RecordType = '3'
group by date

Upvotes: 1

Views: 90

Answers (3)

valex
valex

Reputation: 24144

Also in MSSQL you can use PIVOT

SELECT date, [1] AS Count_unique1,
             [2] AS Count_unique2,
             [3] AS Count_unique3
FROM (SELECT date,recordtype,amount FROM interview) p
PIVOT
(
  COUNT (amount)
  FOR recordtype IN ([1], [2], [3])
) AS pvt
ORDER BY pvt.date;

SQLFiddle demo

Upvotes: 1

Sanal K
Sanal K

Reputation: 733

If RecordType is 1,2 and 3 in all cases this will be enough.

select date, 
sum(RecordType = '1') as Count_unique1,
sum(RecordType = '2') as Count_unique2,
sum(RecordType = '3') as Count_unique3
from interview
group by date

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

select 
date, 
sum(case when RecordType = '1' then 1 else 0 end) as Count_unique1,
sum(case when RecordType = '2' then 1 else 0 end) as Count_unique2,
sum(case when RecordType = '3' then 1 else 0 end) as Count_unique3
from interview
group by date

Upvotes: 3

Related Questions