CODEWITHSUNDEEP
sql-server
user1464139
user1464139

Reputation:

How can I get a Group By to show me a 0 if no rows match?

I have two tables: Phrase and PhraseCategory

They are linked with Phrase.CategoryId == PhraseCategory.PhraseCategoryShortId

CREATE TABLE [dbo].[Phrase] (
    [PhraseId]     UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [English]      NVARCHAR (250)   NOT NULL,
    [CategoryId]   INT              NULL,
    PRIMARY KEY CLUSTERED ([PhraseId] ASC)
);


CREATE TABLE [dbo].[PhraseCategory] (
    [PhraseCategoryShortId] INT              IDENTITY (1, 1) NOT NULL ,
    [Name]                  VARCHAR (100)    NOT NULL,
    PRIMARY KEY CLUSTERED ([PhraseCategoryShortId] ASC)
);

Can someone help give me some advice on how I can join these so that I get a report looking like this:

PhraseCategory.Name        Qty

Here's what I have so far:

SELECT PhraseCategory.name, count(*) AS qty
  FROM Phrase
  LEFT OUTER JOIN PhraseCategory
    ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId
 GROUP BY PhraseCategory.name
 ORDER BY PhraseCategory.name

The problem for me is that I want it to show the Phrase Category name and a 0 if there are no rows with that category. So far I cannot get this to work.

Upvotes: 0

Views: 37

Answers (4)

SqlZim
SqlZim

Reputation: 38023

If you want to see counts for both phrases that do not have a category, and categories that do not have phrases, you can use a full outer join.

Since Phrase.CategoryId can be null, you might want to return a string instead of null, e.g. 'No Category'.

select 
   Category = coalesce(pc.name,'No Category')
 , qty = count(p.PhraseId)
from Phrase p 
  full outer join PhraseCategory pc 
    on p.CategoryId = pc.PhraseCategoryShortId
group by pc.name
order by pc.name;

returns:

+-------------+-----+
|  Category   | qty |
+-------------+-----+
| No Category |   1 |
| ColumnNames |   3 |
| Functions   |   0 |
| KeyWords    |   2 |
| Users       |   1 |
+-------------+-----+

test setup: http://rextester.com/FUBSY27286

insert into phrase (English, CategoryId) values
 ('who',null) /* no category */
,('select',1)
,('from',1)
,('PhraseId',2)
,('English',2)
,('CategoryId',2)
,('Anne',3);

insert into PhraseCategory (Name) values
 ('KeyWords')
,('ColumnNames')
,('Users')
,('Functions') /*no phrases */;

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Your join is in the wrong order, and you need to count only the records in Phrase, not both tables:

SELECT PhraseCategory.name, count(Phrase.*) AS qty
  FROM PhraseCategory
  LEFT OUTER JOIN Phrase
    ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId
 GROUP BY PhraseCategory.name
 ORDER BY PhraseCategory.name

Of course, you could change the left join to a right join and keep the same order of tables:

SELECT PhraseCategory.name, count(Phrase.*) AS qty
  FROM Phrase
  RIGHT OUTER JOIN PhraseCategory
    ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId
 GROUP BY PhraseCategory.name
 ORDER BY PhraseCategory.name

Upvotes: 1

Yasar Khalid
Yasar Khalid

Reputation: 71

try IFNULL 

SELECT PhraseCategory.name, IFNULL(count(*),0) AS qty
FROM Phrase
LEFT OUTER JOIN PhraseCategory
ON Phrase.CategoryId = PhraseCategory.PhraseCategoryShortId
GROUP BY PhraseCategory.name
ORDER BY PhraseCategory.name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you have the LEFT JOIN backwards:

SELECT pc.name, count(p.CategoryId) AS qty
FROM PhraseCategory pc LEFT JOIN
     Phrase p
     ON p.CategoryId = pc.PhraseCategoryShortId
GROUP BY pc.name
ORDER BY pc.name;

You seem to want everything in PhraseCategory, so it should be the first table in the LEFT JOIN. Also note that the COUNT() changed, so it counts the matches in the second table (this is how it returns 0).

Upvotes: 1

Related Questions