Mfcttrf

Tiny image scraper for xkcd.com

Why didn't Thatcher give Hong Kong to Taiwan?

Punishment in pacifist society

Measure LiPo Battery Cells with Voltage Divider?

How to use multiple criteria for -find

Why not use futuristic pavise ballistic shields for protection?

Design of 50 ohms RF trace for 2.4GHz...Double layer FR-4 PCB

Lumix G7: Raw photos only in 1920x1440, no higher res available

If the UK government illegally doesn't ask for article 50 extension, can parliament do it instead?

What is a "fat pointer" in Rust?

How to disambiguate between various meditation practices?

Declaring 2 (or even multi-) dimensional std::arrays elegantly

How did Gollum know Sauron was gathering the Haradrim to make war?

Do index funds really have double-digit percents annual return rates?

What is the maximal acceptable delay between pilot's input and flight control surface actuation?

Is it rude to ask my opponent to resign an online game when they have a lost endgame?

co-son-in-law or co-brother

Why don't they build airplanes from 3D printer plastic?

My boss says "This will help us better view the utilization of your services." Does this mean my job is ending in this organisation?

Remove ads in Viber for PC

Is mathematics truth?

In chocolate terminology, what is the name of thinly sliced leaf-shaped toppings made from hot, smooth chocolate, used to form flower petals?

One hour 10 min layover in Newark; International -> Domestic connection. Enough time to clear customs?

Does secure hashing imply secure symmetric encryption?

How to get all months in a query where one month has no matches?

New query slower even if it has less logical reads than the old oneWhy SELECT COUNT() query execution plan includes left joined table?How to fill gaps with calendar table, count and group byNeed Help With SQL Server 2008 Join With 3 TablesFilling Empty Values when Calculating Standard DeviationSum by Implicit GroupAre there any other ways to get results from Two tables without using Sub QueryWill table partitions help me?How to bring data from two tables that could/could not be present in one of themSlow query on large table with 50M plus data (especially with larger offset)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

1

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?

sql-server

share|improve this question

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If I’ve understood correctly you could use a RIGHT JOIN instead of LEFT (or swap to select from tblMes first if that’s where the months are listed).
  
  – JShark
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @JShark Yes, you could swap the tables, or change to RIGHT JOIN, but you also have to change the WHERE clause to AND.
  
  – Aaron Bertrand♦
  7 hours ago
  

  
  
  
  

add a comment | 

1

1

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?

sql-server

share|improve this question

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If I’ve understood correctly you could use a RIGHT JOIN instead of LEFT (or swap to select from tblMes first if that’s where the months are listed).
  
  – JShark
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @JShark Yes, you could swap the tables, or change to RIGHT JOIN, but you also have to change the WHERE clause to AND.
  
  – Aaron Bertrand♦
  7 hours ago
  

  
  
  
  

add a comment | 

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?

sql-server

share|improve this question

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

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

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

share|improve this question

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

share|improve this question

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

asked 8 hours ago

ffuentesffuentes

11866 bronze badges

2 Answers
2

active

oldest

votes

6

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;

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, exactly. tblMes is a table with month ids and names
  
  – ffuentes
  7 hours ago
  

  
  
  
  

add a comment | 

2

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;

share|improve this answer

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f246795%2fhow-to-get-all-months-in-a-query-where-one-month-has-no-matches%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

6

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;

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, exactly. tblMes is a table with month ids and names
  
  – ffuentes
  7 hours ago
  

  
  
  
  

add a comment | 

6

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;

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, exactly. tblMes is a table with month ids and names
  
  – ffuentes
  7 hours ago
  

  
  
  
  

add a comment | 

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;

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

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;

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

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

answered 8 hours ago

Aaron Bertrand♦Aaron Bertrand

160k1919 gold badges319319 silver badges523523 bronze badges

2

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;

share|improve this answer

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

add a comment | 

2

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;

share|improve this answer

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

add a comment | 

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;

share|improve this answer

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

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;

share|improve this answer

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges

answered 8 hours ago

LennartLennart

14.5k22 gold badges1313 silver badges4444 bronze badges