Elhendriks Elhendriks - 14 days ago 6
SQL Question

SQL Request With Sum and multiple Where conditions

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?

Answer

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
Comments