• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

SQL Count on joined table

Hello,

Has been a long day, and I can't for the life of me remember how to do this,

I have 2 tables..

dbo.direct-category (C)
[ID] [int] IDENTITY(1,1) NOT NULL,
[Category-label] [nvarchar](50) NOT NULL,
[category-order] [int] NOT NULL,
[Display] [char](1) NOT NULL

AND

dbo.Direct-Product-Category (P)
[ID] [int] IDENTITY(1,1) NOT NULL,
[Category-ID] [int] NOT NULL,
[Product-ID] [int] NOT NULL

The tables are joined by C.ID = P.[Category-ID]

I want to build a recordset that gives me the following

C.[Category-label]
C.[Category-Order]
C.[Display]
Count(P.Category-ID] (IF NULL 0)

Can't for the life of me remember how to do this...

Please advise -

Thank you
Select
  C.[Category-label],
  Count(P.[Category-ID]),
  C.[category-order],
  C.Display
  from dbo.[Direct-Category] C
  inner join dbo.[Direct-Product-Category] P
  on P.[Category-ID] = C.ID
  group by C.ID

Open in new window

0
garethtnash
Asked:
garethtnash
1 Solution
 
BartVxCommented:
COUNT(CASE WHEN P.CategoryID IS NULL THEN 0 ELSE 1 END)
0
 
BartVxCommented:
correction SUM, not COUNT :)
0
 
garethtnashAuthor Commented:
Thanks...

This -

  Select
  C.[Category-label],
  COUNT(CASE WHEN P.[Category-ID] IS NULL THEN 0 ELSE 1 END) ,
  C.[category-order],
  C.Display
  from dbo.[Direct-Category] C
  inner join dbo.[Direct-Product-Category] P
  on P.[Category-ID] = C.ID
  group by C.ID

Open in new window


 gives me -

Msg 8120, Level 16, State 1, Line 2
Column 'dbo.Direct-Category.Category-label' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
tim_csCommented:
Your group by needs to be....

GROUP BY
   C.[Category-label],
   C.[category-order],
   C.Display
0
 
BartVxCommented:
Try this:

Select  
C.[Category-label]
,  SUM(CASE WHEN P.[Category-ID] IS NULL THEN 0 ELSE 1 END) 
,  C.[category-order]
,  C.Display  
from dbo.[Direct-Category] C  
inner join dbo.[Direct-Product-Category] P  
on P.[Category-ID] = C.ID  
group by C.[Category-label], C.[category-order], C.Display

Open in new window

0
 
rushShahCommented:
try this,
Select
  C.[Category-label],
  COUNT(CASE WHEN P.[Category-ID] IS NULL THEN 0 ELSE 1 END) ,
  C.[category-order],
  C.Display
  from dbo.[Direct-Category] C
  inner join dbo.[Direct-Product-Category] P
  on P.[Category-ID] = C.ID
  group by C.[Category-label],
C.[category-order],
  C.Display

Open in new window

0
 
garethtnashAuthor Commented:
Thanks Bart
0
 
garethtnashAuthor Commented:
Sorry Bart, one last thing,...

If there are no records in dbo.[Direct-Product-Category] P the line isn't returned in the select statement ..

?

Thank you
0
 
BartVxCommented:
Change the INNER JOIN to a LEFT JOIN and it should work
0
 
garethtnashAuthor Commented:
Thank you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now