SQL query to get number of products for each category
0
I have a database for an e-commerce store. I'm trying to know the number of active products for each category.
Code for that:
SELECT c.id_category, COUNT(cp.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product
WHERE p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
However, it is not showing categories with 0 products. What am I missing?
mysql sql
edited Nov 14 '18 at 0:23
Homam
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
add a comment |
0
I have a database for an e-commerce store. I'm trying to know the number of active products for each category.
Code for that:
SELECT c.id_category, COUNT(cp.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product
WHERE p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
However, it is not showing categories with 0 products. What am I missing?
mysql sql
edited Nov 14 '18 at 0:23
Homam
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
add a comment |
0
0
0
I have a database for an e-commerce store. I'm trying to know the number of active products for each category.
Code for that:
SELECT c.id_category, COUNT(cp.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product
WHERE p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
However, it is not showing categories with 0 products. What am I missing?
mysql sql
edited Nov 14 '18 at 0:23
Homam
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
I have a database for an e-commerce store. I'm trying to know the number of active products for each category.
Code for that:
SELECT c.id_category, COUNT(cp.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product
WHERE p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
However, it is not showing categories with 0 products. What am I missing?
mysql sql
mysql sql
edited Nov 14 '18 at 0:23
Homam
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
edited Nov 14 '18 at 0:23
Homam
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
edited Nov 14 '18 at 0:23
Homam
192
edited Nov 14 '18 at 0:23
Homam
192
edited Nov 14 '18 at 0:23
Homam
192
192
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
asked Nov 14 '18 at 0:03
notGeeknotGeek
55221027
55221027
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
2
Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.
SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't countingp.id_product. I'll update my answer appropriately.
– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows inps_productassociated with a category there should also be no rows inps_category_productfor that category. Socp.id_productshould also beNULLin those cases.
– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should countp.id_product.
– Nick
Nov 14 '18 at 1:34
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291252%2fsql-query-to-get-number-of-products-for-each-category%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.
SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't countingp.id_product. I'll update my answer appropriately.
– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows inps_productassociated with a category there should also be no rows inps_category_productfor that category. Socp.id_productshould also beNULLin those cases.
– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should countp.id_product.
– Nick
Nov 14 '18 at 1:34
add a comment |
2
Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.
SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't countingp.id_product. I'll update my answer appropriately.
– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows inps_productassociated with a category there should also be no rows inps_category_productfor that category. Socp.id_productshould also beNULLin those cases.
– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should countp.id_product.
– Nick
Nov 14 '18 at 1:34
add a comment |
2
2
2
Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.
SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
Remove WHERE p.active=1 which will fail for any categories which have no products (causing the query to return no rows for those categories) and move that condition into the ON clause for that LEFT JOIN. That way you will still get a NULL row for those categories, which will allow them to show as having 0 products.
SELECT c.id_category, COUNT(p.id_product) AS nproducts
FROM ps_category AS c
LEFT JOIN ps_category_product AS cp ON cp.id_category=c.id_category
LEFT JOIN ps_product AS p ON p.id_product=cp.id_product AND p.active=1
GROUP BY c.id_category
ORDER BY nproducts ASC
Note that to ensure you only count active products you need to count p.id_product, not cp.id_product.
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
edited Nov 14 '18 at 1:35
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
answered Nov 14 '18 at 0:08
NickNick
32.6k121942
32.6k121942
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't countingp.id_product. I'll update my answer appropriately.
– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows inps_productassociated with a category there should also be no rows inps_category_productfor that category. Socp.id_productshould also beNULLin those cases.
– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should countp.id_product.
– Nick
Nov 14 '18 at 1:34
add a comment |
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't countingp.id_product. I'll update my answer appropriately.
– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows inps_productassociated with a category there should also be no rows inps_category_productfor that category. Socp.id_productshould also beNULLin those cases.
– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should countp.id_product.
– Nick
Nov 14 '18 at 1:34
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
I think I'll also need to change from COUNT(cp.id_product) to COUNT(p.id_product) to avoid counting NULL values. Does it makes sense?
– notGeek
Nov 14 '18 at 0:35
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't counting
p.id_product. I'll update my answer appropriately.– Nick
Nov 14 '18 at 1:17
@notGeek Yes, that does make sense. I hadn't noticed at first that you weren't counting
p.id_product. I'll update my answer appropriately.– Nick
Nov 14 '18 at 1:17
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows in
ps_product associated with a category there should also be no rows in ps_category_product for that category. So cp.id_product should also be NULL in those cases.– Nick
Nov 14 '18 at 1:20
@notGeek actually no thinking about it you shouldn't have to, as if there are no rows in
ps_product associated with a category there should also be no rows in ps_category_product for that category. So cp.id_product should also be NULL in those cases.– Nick
Nov 14 '18 at 1:20
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
You're right but my point is that I only want to count if the product is active so the row can exist in both ps_category_product and in ps_products but will be filtered by the p.active=1 when we are joining with ps_product table. Does this makes sense?
– notGeek
Nov 14 '18 at 1:30
@notGeek Yes indeed. So you should count
p.id_product.– Nick
Nov 14 '18 at 1:34
@notGeek Yes indeed. So you should count
p.id_product.– Nick
Nov 14 '18 at 1:34
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53291252%2fsql-query-to-get-number-of-products-for-each-category%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
