SQL Request With Sum and multiple Where conditions

Question

My table contains data about visits, date of the visits, type and number of visitors. It looks like:

| Date_Visit | Type  | Nb_Visitors |
 04-05-2015   Intern    3
 08-09-2015   Extern    10
 13-09-2015   Intern    2
 17-09-2015   Intern    6
    ...        ...      ...

I would like to have as output something like this:

|DateMonth | Nb_Visit_Extern | Nb_Visitors_Extern | Nb_Visit_Intern | Nb_Visitors_Intern |
 05-2015     1                 3                   0                  0
 09-2015     1                 10                  2                  8

My query look like:

SELECT CONVERT (VARCHAR(7), [Date_Visit], 20) As DateMonth,
COUNT(*) Nb_Visit_Extern,
SUM(Nb_Visitors) Nb_Visitors_Extern
From MyTable
Where [Type] = 'Extern'
GROUP BY CONVERT (VARCHAR(7), [Date_Visit], 20)
ORDER BY CONVERT (VARCHAR(7), [Date_Visit], 20) ASC

SELECT CONVERT (VARCHAR(7), [Date_Visit], 20) As DateMonth,
COUNT(*) as Nb_Visit_Intern,
SUM(Nb_Visitors) as Nb_Visitors_Intern
From MyTable
Where [Type] = 'Intern'
GROUP BY CONVERT (VARCHAR(7), [Date_Visit], 20)
ORDER BY CONVERT (VARCHAR(7), [Date_Visit], 20) ASC

I tried to use the CASE condition but it didn't work with the SUM statement. How can I do?

Pooja-G · Accepted Answer · 2016-11-21 10:53:35Z

1

You can use simple Count and Sum function of SQL.

    SELECT CONVERT (VARCHAR(7), [Date_Visit], 20) As DateMonth,
COUNT(case when Type = 'Extern' then 1 else 0 end) Nb_Visit_Extern,
SUM(case when Type = 'Extern' then [Nb_Visitors] else 0 end) Nb_Visitors_Extern,
COUNT(case when Type = 'Intern' then 1 else 0 end) Nb_Visit_Intern,
SUM(case when Type = 'Intern' then [Nb_Visitors] else 0 end) Nb_Visitors_Intern
GROUP BY CONVERT (VARCHAR(7), [Date_Visit], 20)
ORDER BY CONVERT (VARCHAR(7), [Date_Visit], 20) ASC

answered Nov 21, 2016 at 10:53

Pooja-G

5184 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pooja-G Over a year ago

If you found any problem, please mention me. Happy to discuss.

Rahul · Accepted Answer · 2016-11-21 10:38:08Z

0

You should use a case expression like

SUM(case when Type = 'Extern' then 1 else 0 end) as Nb_Visitors_Extern

answered Nov 21, 2016 at 10:38

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL Request With Sum and multiple Where conditions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related