CODEWITHSUNDEEP
mysqlsql
Rasmus Bengtsson
Rasmus Bengtsson

Reputation: 43

Counting rows in referencing parents and children

I have a table that looks like this:

Categories:
cId | Name                    | Parent
----+-------------------------+-------
1   | Parent One              | NULL
2   | Child of 1st Parent     | 1
3   | Parent Two              | NULL
4   | Child of 1st Parent     | 1
5   | Child of 2nd Parent     | 2

The table does not represent a heirarchy: Every item is either a child or a parent, but not both.

And one table like this:

Posts:
pId | Name                    | cID
----+-------------------------+-------
1   | Post 1                  | 1
2   | Post 2                  | 2
3   | Post 3                  | 2
4   | Post 4                  | 3

I'd like to run a query on it that returns this:

cId | Count  
---+---------
1   | 3
2   | 2
3   | 1
4   | 0
5   | 0

Count is the number of posts connected to the category.

All categories should be returned.

Parent categories should have the count of the category + child categories sum. (this is one of the things I'm having problem with)

Child categories should have the category sum.

How should I do this?

Upvotes: 4

Views: 1418

Answers (2)

sgeddes
sgeddes

Reputation: 62861

From your expected results, it looks like you don't care about grandchildren and lower, in which case, this should work. To get the correct parent count, I'm checking for Parent IS NULL or Count(children) > 0, in which case, I'm adding 1:

SELECT c.cId, CASE WHEN C.Parent IS NULL OR COUNT(C2.cId) > 0 THEN 1 ELSE 0 END + 
    COUNT(C2.cId) TotalCount
FROM Categories C
    LEFT JOIN Categories C2 on c.cId = c2.Parent
GROUP BY c.cId

Here is some sample fiddle: http://www.sqlfiddle.com/#!2/b899f/1

And the results:

CID  TOTALCOUNT
1    3
2    2
3    1
4    0
5    0

---EDIT---

From reading your comments, it looks like you want something like this:

SELECT c.cId, 
    COUNT(DISTINCT P.pId) + COUNT(DISTINCT P2.pId) TotalCount
FROM Categories C
   LEFT JOIN Posts P ON C.CId = P.CId
    LEFT JOIN Categories C2 on c.cId = c2.Parent
   LEFT JOIN Posts P2 ON C2.CId = P2.CId
GROUP BY c.cId

http://www.sqlfiddle.com/#!2/eb0d2/3

Upvotes: 3

Art
Art

Reputation: 5792

This is general hint. I do not know if analytic functions and partitioning available in MySQL but you can partition your output by categories then count and sum up within categories. Do some research about analytic functions and partition by clause. General example of what I meant - output is partitioned by deptno and ordered. Also, max hiredate determined within partition - replace max with count, sum etc... in your case:

SELECT * FROM 
(  
SELECT deptno
     , empno
     , ename
     , sal
     , RANK() OVER (PARTITION BY deptno ORDER BY sal desc) rnk 
     , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal desc) rno 
     , MAX(hiredate) OVER (PARTITION BY deptno ORDER BY deptno) max_hire_date
 FROM emp_test
ORDER BY deptno
)
--WHERE rnk = 1
ORDER BY deptno, sal desc
/

DEPTNO    EMPNO    ENAME    SAL    RNK    RNO    MAX_HIRE_DATE
--------------------------------------------------------------------
10        7839    KING      5000    1      1    1/23/1982
10        7782    CLARK     2450    2      2    1/23/1982
10        7934    MILLER    1300    3      3    1/23/1982
20        7788    SCOTT     3000    1      1    1/28/2013
20        7902    FORD      3000    1      2    1/28/2013
20        7566    JONES     2975    3      3    1/28/2013

Upvotes: 0

Related Questions