SQL - Using Group By and Count to collect the total of unique two-field combinations

Question

I have two fields (project and version) from two different tables, and I am trying to get the total of entries that match unique project/version combinations, however the versions in some projects match up with versions in other projects (Project 1 has version 1.0, and so does Project 2) so my SQL statement isn't working:

SELECT TOP 100 PERCENT project.PNAME AS PROJECT, version.VNAME AS VERSION, COUNT(version.VNAME)
FROM issue INNER JOIN
project ON issue.PROJECT = project.ID INNER JOIN
version ON issue.VERSION = version.ID
GROUP BY project.PNAME, version.VNAME

I thought I could use something like

COUNT(project.PNAME, version.VNAME)

but I was wrong... I'm sure the answer is easy but I cannot find it... any help?

Answer 1

I'm not certain about SQL Server but normally you can use COUNT(*) to count the number of grouped rows. Simple example giving the count per PNAME:

SELECT COUNT(*), project.PNAME FROM project GROUP BY project.PNAME

Answer 2

try this:

SELECT p.PNAME PROJECT, v.VNAME VERSION,
    COUNT(*) projectVersionCount
FROM issue i
   JOIN project p ON i.PROJECT = p.ID 
   JOIN version v ON i.VERSION = v.ID
GROUP BY project.PNAME, version.VNAME

Answer 3

What is wrong with:

SELECT project.PNAME, version.VNAME, COUNT(*)
FROM issue 
INNER JOIN project ON issue.PROJECT = project.ID 
INNER JOIN version ON issue.VERSION = version.ID
GROUP BY project.PNAME, version.VNAME

Wouldn't that get you all combinations of project name / version name and the count of those combinations??

Marc

Answer 4

Does this get you what you want?

SELECT  project.PNAME AS PROJECT, version.VNAME AS VERSION, COUNT(*)
FROM issue 
INNER JOIN project 
 ON issue.PROJECT = project.ID 
 INNER JOIN version 
  ON issue.VERSION = version.ID
GROUP BY project.PNAME, version.VNAME

If not show the results you got versus what you think you should have gotten.