How to get all months in a query where one month has no matches? I need to obtain every month by year (to fill up a graph). However, I found out that the first month had no data whatsoever and I wanna reflect that. The query is this one:

SELECT tblMes.id, COUNT(*) AS ingreso
FROM tblTicket
LEFT JOIN tblMes ON MONTH(tblTicket.fechaIngreso) = tblMes.id
WHERE year(fechaIngreso) = 2017
GROUP BY tblMes.id
ORDER BY tblMes.id asc


The result of the query is this one:

Month | Qty
--------------
"2"   | "1066"
"3"   | "1395"
"4"   | "761"
"5"   | "1316"
"6"   | "879"
"7"   | "1039"
"8"   | "1099"
"9"   | "577"
"10"  | "1064"
"11"  | "1268"
"12"  | "1188"


The first row should be "1" | "0"

Why is the left join not enough?
I think you just had your tables backwards (the first table is all the rows you want, the outer table is all the rows that might exist). You also need the outer table clauses to be part of the join criteria (ON), rather than filter criteria (WHERE), otherwise your outer join turns into an inner join.

SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM dbo.tblMes AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON MONTH(t.fechaIngreso) = m.id
AND year(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;


This is assuming tblMes is just a table with 12 rows, with one row for each of the 12 months. You can also do this without that table pretty easily:

;WITH Months(id) AS
(
  SELECT 1 UNION ALL SELECT id+1 FROM Months WHERE id &lt;= 11
)
SELECT m.id, COUNT(t.fechaIngreso) AS ingreso
FROM Months AS m
LEFT OUTER JOIN dbo.tblTicket AS t
ON m.id = MONTH(t.fechaIngreso)
AND YEAR(t.fechaIngreso) = 2017
GROUP BY m.id
ORDER BY m.id;


On modern enough versions of SQL Server, and with an index on fechaIngreso, this may perform better:

ON t.fechaIngreso &gt;= DATEFROMPARTS(2017, m.id, 1)
  AND t.fechaIngreso &lt; DATEADD(MONTH, 1, DATEFROMPARTS(2017, m.id, 1))


The reason is that MONTH() and YEAR() functions will force a scan, because the result of that computation will have to be evaluated for every single row. Avoiding this work is preferable, IMHO, even if you don't have a supporting index on that column now.