CODEWITHSUNDEEP
sqlsql-server
Dheeraj Patnaik
Dheeraj Patnaik

Reputation: 375

How to get count in sql

I have a 2 tables as follows

| Id |   email |
|----|---------|
|  1 | [email protected] |
|  2 | [email protected] |
|  3 | [email protected] |
|  4 | [email protected] |
|  5 | [email protected] |
|  6 | [email protected] |
|  7 | [email protected] |

-

| Id | names |
|----|-------|
|  1 |  audi |
|  2 |  ford |
|  3 |  ford |
|  4 |  ford |
|  5 |  audi |
|  6 |  benz |
|  7 |   bmw |
|  1 |   bmw |
|  2 |  audi |

Now, I want to display the columns of email,count of email and ReqType, where ReqType column shows as required if names table contains either "Audi or ford" else "Not-Req"

My query is:

select  
    email, count(email) as Email,
    case
       when t2.names in ('Audi', 'ford')
          then 'req'
          else 'Not-req'
    end as 'ReqType'
from
    t1
inner join      
    t2 on t1.Id = t2.Id
group by     
    email, names

But it is showing as:

|   email | Email | ReqType |
|---------|-------|---------|
| [email protected] |     1 |     req |
| [email protected] |     1 | Not-req |
| [email protected] |     1 |     req |
| [email protected] |     1 |     req |
| [email protected] |     1 | Not-req |
| [email protected] |     2 |     req |
| [email protected] |     1 |     req |
| [email protected] |     1 | Not-req |

Required output is

|   email | Email | ReqType |
|---------|-------|---------|
| [email protected] |     2 |     req |
| [email protected] |     1 | Not-req |
| [email protected] |     3 |     req |
| [email protected] |     1 | Not-req |
| [email protected] |     1 |     req |
| [email protected] |     1 | Not-req |

How to achieve this?

SqlDemo

Upvotes: 0

Views: 64

Answers (2)

Tiago Oliveira de Freitas
Tiago Oliveira de Freitas

Reputation: 546

@jarlh is right:

select email,
        count(email) as Email,
        case when t2.names in ('Audi','ford')
                then 'req'
                else 'Not-req'
            end as 'ReqType'
    from t1
      inner join t2 on t1.Id = t2.Id
    group by email, case when t2.names in ('Audi','ford')
                            then 'req'
                            else 'Not-req'
                        end

Upvotes: 1

LDMJoe
LDMJoe

Reputation: 1589

Create your ReqType field at the same level as your actual columns, then do your count.

SELECT
    [email],
    COUNT([email]) AS [Email],
    [ReqType]
FROM
    (
    select          
        email,
        case when t2.names in ('Audi','ford') then 
            'req'
        else
            'Not-req'
        end 
        as 'ReqType'
    from
        t1
        inner join t2 on t1.Id = t2.Id
    ) DerivedWithReqType
GROUP BY
    [email],
    [ReqType]

Upvotes: 1

Related Questions