CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Retrieve matching rows using join

This is a simplified version of my problem.

I have table like below

Id   Name       SNumber
100  XYZ        123
100  XYZ        123
101  ABC        123
103  QAZ        123
100  XYZ        971
100  XYZ        872
100  XYZ        659
102  PQR        145
102  PQR        707
103  QAZ        421

I want to count rows having Snumber as '123' ie Total column and rows having Snumber not as '123' i.e. otherTotal column

Id   Name       Total   OtherTotal
100  XYZ        2           3
101  ABC        1           0
102  PQR        0           2
103  QAZ        1           1

What I am doing is using join

Select xx.*,otherTotal
From
    ( Select Id,Name,count(*) as Total 
      From  table
      Where Snumber like '123'
      Group By id,name
     )xx

Inner join

     ( Select Id,Name,count(*) as otherTotal
      From  table
      Where Snumber not like '123'
      Group By id,name
     )yy

On xx.Id=yy.Id

But this will only return rows if particular Id has both Snumber as 123 and not as 123

Data returned is like below

Id   Name       Total   OtherTotal
100  XYZ        2           3
103  QAZ        1           1

Now there is no guarntee that a particular Id will always have Snumber as 123 so I can't use Left or Right join. How to solve this quagmire ? Giggity

Upvotes: 2

Views: 76

Answers (3)

The Hill Boy
The Hill Boy

Reputation: 162

try this one.

DECLARE @TABLE TABLE (ID INT,   NAME VARCHAR(40), SNUMBER INT)
INSERT INTO @TABLE
VALUES 
(100  ,'XYZ',        123),
(100  ,'XYZ',        123),
(101  ,'ABC',        123),
(103  ,'QAZ',        123),
(100  ,'XYZ',        971),
(100  ,'XYZ',        872),
(100  ,'XYZ',        659),
(102  ,'PQR',        145),
(102  ,'PQR',        707),
(103  ,'QAZ',        421)

SELECT 
    ID, 
    NAME, 
    (
        SELECT 
            COUNT(SNUMBER) FROM @TABLE B
        WHERE 
            SNUMBER = '123' AND A.ID = B.ID
    ) AS TOTAL,
    (
        SELECT 
            COUNT(SNUMBER) FROM @TABLE B
        WHERE 
            SNUMBER <> '123' AND A.ID = B.ID
    ) AS OTHERTOTAL
FROM 
    @TABLE A
GROUP BY ID, NAME

Upvotes: 1

roman
roman

Reputation: 117485

select
    Id, Name,
    sum(case when SNumber = 123 then 1 else 0 end) as Total,
    sum(case when SNumber <> 123 then 1 else 0 end) as OtherTotal
from Table1
group by Id, Name
order by Id

or 

select
    Id, Name,
    count(*) - count(nullif(SNumber, 123)) as Total,
    count(nullif(SNumber, 123)) as OtherTotal
from Table1
group by Id, Name
order by Id

sql fiddle demo

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

Try this:

SELECT id, name,
  COUNT(CASE WHEN SNumber = 123 THEN 1 END) Total,
  COUNT(CASE WHEN SNumber <> 123 THEN 1 END) OtherTotal
FROM t
GROUP BY id, name
ORDER BY id

Fiddle here.

Upvotes: 4

Related Questions