Xjyuk

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 <= 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;

Since you did not provide either table definitions nor sample data, it's not easy to guess. The easiest way is to add a default row for each month, and then pick the max for that month. Something more elegant is likely possible, but it is impossible to guess what that would be:

SELECT MONTH, MAX(CNT)
FROM (
 SELECT tblMes.id AS MONTH, COUNT(*) AS CNT
 FROM tblTicket
 LEFT JOIN tblMes ON MONTH(tblTicket.fechaIngreso) = tblMes.id
 WHERE year(fechaIngreso) = 2017
 GROUP BY tblMes.id
 UNION SELECT 1, 0
 UNION SELECT 2, 0
 UNION ...
 ...
) AS X
GROUP BY MONTH
ORDER BY MONTH;