Clever Geek Handbook

SQL - Using Group By and Count to Collect a Common Unique Combination of Two Fields

I have two fields (project and version) from two different tables, and I am trying to get the total number of records that match unique combinations of projects and versions, however, versions in some projects match versions in other projects (Project 1 has version 1.0 as well project 2), so my SQL statement doesn't work:

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 am sure the answer is simple but i cant find it ... any help?

+2


source to share


4 answers


I'm not sure about SQL Server, but you can usually use COUNT (*) to count the number of grouped rows. A simple example giving a bill to PNAME:



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


source


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
+4


source


What's wrong:

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

Could you get all the combinations of the project name / name and the number of these combinations?

Mark

+2


source


Do you get 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 you are not showing the results you get compared to what you think you should get.

+2


source






More articles:


All Articles