Get Count of Each Distinct Pair-CodePudding

I am trying to create a query that will give me all combinations of original sources and new sources, along with how many times each occur. What I have below seems to do the first part of giving me all of the different pairs, but I am struggling with getting it to display how many occurrences each have.

SELECT DISTINCT original_source, new_source
FROM sources
WHERE identifier = 1
ORDER BY original_source

CodePudding user response：

Just to expand on Larnu's comment

SELECT original_source
      ,new_source
      ,Cnt = count(*)
FROM  sources
WHERE identifier = 1
GROUP BY original_source
        ,new_source
ORDER BY original_source

CodePudding user response：

You need to use GROUP BY

SELECT original_source, new_source, count(1) [Count]
FROM sources
WHERE identifier = 1
GROUP BY original_source, new_source
ORDER BY original_source