view raw
Elhendriks Elhendriks - 6 months ago 30
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