CODEWITHSUNDEEP
sqljoin
Ash S
Ash S

Reputation: 119

How to join two tables and count records SQL

table 1 is maintable_KQPPJ : contains GroupID, Year, Name, VendorID. This table contains multiple records with the same GroupID table 2 is cb_vendorinformation: contains GroupID and CompanyName

I would like to join both tables on GroupID. The output should only have GroupID, CompanyName, and Count. The Count is the distinct count of GroupID in maintable_KQPPJ.

I have the following code but it doesn't really give me the output I'm looking for.

SELECT  maintable_KQPPJ.GROUPID, cb_vendorinformation.CompanyName, count(distinct maintable_KQPPJ.GROUPID)
FROM maintable_KQPPJ
 JOIN cb_vendorinformation ON maintable_KQPPJ.GROUPID=cb_vendorinformation.GROUPID

maintable_KQPPJ:

GroupID  Year VendorID Name
26       2019 9999     John
26       2020 2345     Jane 
6        2018 3244     Jack
36       2021 3245     Jill

cb_vendorinformation:

GroupID CompanyName 
26      Walmart    
6       Target     
36      Kroger

The output should look like

GroupID CompanyName Count
26      Walmart     2
6       Target      1
36      Kroger      1

Upvotes: 0

Views: 34

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You need group by and count(*)

SELECT  maintable_KQPPJ.GROUPID
 , cb_vendorinformation.CompanyName
 , count(*)
FROM maintable_KQPPJ
JOIN cb_vendorinformation ON maintable_KQPPJ.GROUPID=cb_vendorinformation.GROUPID
GROUP BY maintable_KQPPJ.GROUPID
 , cb_vendorinformation.CompanyName

Upvotes: 1

Related Questions