Join SQL table using the same row but different view/column output-CodePudding

Edit: The initial issue is addressed with the answer below. I have one further question - Now that I see the "Linked" and "Not Linked" counts, I am trying to get percentage of "Linked" by COUNT("Article Link")/COUNT(*) as "Percentage", but it shows just '0' or '1' and not actual percentage.

--------------Original issue------------

I have the below table.

CaseNumber  Owner   Article Link
ABC1234     John    Linked
ABC2345     Alex    Linked
ABC3456     John    Linked
ABC4567     Maria   Linked
ABC5678     Maria   
ABC6789     John    
ABC7900     Alex    
ABC9011     Issac   Linked
ABC10122    Issac   Linked
ABC11233    Issac   Linked
ABC12344    Issac   Linked
ABC13455    Issac   Linked
ABC14566    Maria   Linked
ABC15677    Maria   Linked
ABC16788    Maria   Linked
ABC17899    John    Linked
ABC19010    John

And I am trying to get an output like the below, i.e. group by "Owner" with the count of "Linked" and "Not Linked".

**Owner Linked  Not Linked**
Alex    1       1
Issac   5       
John    3       2
Maria   4       1

I have tried the below and this gives result individually, but a self join or and inner/left join doesn't help either.

SELECT  Owner, count(Owner) AS "Linked"
FROM Linking_Table 
Where "Article Link"  = 'Linked'
GROUP BY Owner

SELECT  Owner, count(Owner) AS "Not Linked"
FROM Linking_Table 
Where "Article Link" is NULL
GROUP BY Owner

And tried this as well. No luck.

SELECT  Owner, count(Owner) AS "Linked", count(Owner) AS "Not Linked"
FROM Linking_Table 
WHERE "Article Link"  = 'Linked' OR "Article Link" is NULL
GROUP BY Owner

Can anyone suggest, what am I missing or point me t any documentation?

CodePudding user response：

You can use conditional aggregation as below:

Scheman and insert statements:

CREATE TABLE Linking_Table (CaseNumber VARCHAR(20),  Owner VARCHAR(20),   Article_Link VARCHAR(20))


INSERT INTO Linking_Table VALUES('ABC1234','John','Linked');
INSERT INTO Linking_Table VALUES('ABC2345','Alex','Linked');
INSERT INTO Linking_Table VALUES('ABC3456','John','Linked');
INSERT INTO Linking_Table VALUES('ABC4567','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC5678','Maria','');   
INSERT INTO Linking_Table VALUES('ABC6789','John','');    
INSERT INTO Linking_Table VALUES('ABC7900','Alex','');    
INSERT INTO Linking_Table VALUES('ABC9011','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC10122','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC11233','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC12344','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC13455','Issac','Linked');
INSERT INTO Linking_Table VALUES('ABC14566','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC15677','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC16788','Maria','Linked');
INSERT INTO Linking_Table VALUES('ABC17899','John','Linked');
INSERT INTO Linking_Table VALUES('ABC19010','John','');

Query:

SELECT Owner,sum(case when Article_Link='Linked' then 1 end) Linked,
sum(case when Article_Link<>'Linked' then 1 end) Not_Linked  
FROM Linking_Table
group by Owner
order by Owner

Output:

Owner	Linked	Not_Linked
Alex	1	1
Issac	5	null
John	3	2
Maria	4	1

db<>fiddle here

Query to calculate Link Percentage:

 SELECT Owner,sum(case when Article_Link='Linked' then 1 end) Linked,
 sum(case when Article_Link<>'Linked' then 1 end) Not_Linked,
 100*sum(case when Article_Link='Linked' then 1 end)/count(*) inked_percentage
 FROM Linking_Table
 group by Owner
 order by Owner

Output:

Owner	Linked	Not_Linked	linked_percentage
Alex	1	1	50
Issac	5	null	100
John	3	2	60
Maria	4	1	80

db<>fiddle here

CodePudding user response：

COUNT(*) counts all the rows in a group.

COUNT(column_name) counts all the rows where column_name is NOT NULL

SELECT
  Owner,
  COUNT("Article Link") AS "Linked",
  COUNT(*) - COUNT("Article Link") AS "Not Linked"
FROM
  Linking_Table 
WHERE
     "Article Link"  = 'Linked'
  OR "Article Link" IS NULL
GROUP BY
  Owner

CodePudding user response：

firstly let's highlight that it's not a good practice to use space in the column name, and in case you used it you need to add a Backtick '`' symbol at the column name

and about the required query, you can do something like that

SELECT 
  Owner, 
  (
    select 
      count(*) 
    from 
      linking_table 
    WHERE 
      `Article Link` IS NULL 
      and Owner = l1.Owner
  ) as not_linked, 
  (
    select 
      count(*) 
    from 
      linking_table 
    WHERE 
      `Article Link` IS NOT NULL 
      and Owner = l1.Owner
  ) as linked 
FROM 
  `linking_table` l1 
GROUP BY 
  Owner;