Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: Select and count

    Hello,

    In table 1 are categories listed in table 2 are all products listed.
    I would like to know for each category how many products there are in table 2.

    If there are no items in table 2 for a category then the number 0 must be displayed?

    Wat kind of query do I need?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    LEFT OUTER JOIN might be an answer.

    If you want to know more specific,
    please publish DDLs(CREATE TABLE statemens) and INSERT statements to populate the tables.

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    CREATE TABLE prod (
    SLEUTEL1 CHAR(5) NOT NULL,
    SLEUTEL2 CHAR(1) NOT NULL,
    OMS CHAR(20) NOT NULL,
    FSE_VWK CHAR(1) NOT NULL,
    KDE_STS CHAR(1) NOT NULL,
    DTM_FSE_VWK CHAR(8) NOT NULL,
    DTM_LTS_MTT CHAR(8) NOT NULL,
    KDE_SNL_ZTT CHAR(4) NOT NULL,
    FNT_LTS_KTL CHAR(3) NOT NULL,
    SPN_LTS_KTL CHAR(2) NOT NULL,
    KDE_MDK_MTT CHAR(6) NOT NULL
    );

    CREATE INDEX I4718021 ON categ (SLEUTEL1,SLEUTEL2);

    CREATE TABLE product(
    SLEUTEL1 CHAR(5) NOT NULL,
    SLEUTEL2 CHAR(1) NOT NULL,
    OMS CHAR(1) NOT NULL,
    ACF CHAR(1) NOT NULL
    IDT_PTI_GGV CHAR(12) NOT NULL,
    VGN_PRD CHAR(4) NOT NULL DEFAULT '0000'
    );

    CREATE INDEX I1230095 ON product (SLEUTEL1,SLEUTEL2,VGN_PRD);

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    I can't make out how your structures relate or which col is the product name.. but it would be something like:
    SELECT
    PROD_NAME ,
    (SELECT COUNT(*) FROM TABLE2 t2 WHERE t2.KEY = t1.KEY) as ProductCount
    FROM TABLE 1 t1

  5. #5
    Join Date
    Apr 2013
    Posts
    33
    this might have some syntactical errors, but a combination of left outer join and case expression would help...in below example, I have made my own cols and tables for convenience..lol..

    SELECT C.CATEGORY_ID,
    CASE WHEN COUNT(*) IS NOT NULL, THEN COUNT(*) ELSE '0' END FROM
    CATEGORY C LEFT OUTER JOIN PRODUCT P ON P.CATEGORY_ID=C.CATEGORY_ID GROUP BY C.CATEGORY_ID WITH UR

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Two issues.

    (1) COUNT(*) in Manidba49's query may show 1 even if no product was for a category.
    So, use COUNT(p.product_id), like ...
    Code:
    SELECT c.category_id
         , COUNT(p.product_id) AS product_count
     FROM  category AS c
     LEFT  OUTER JOIN
           product  AS p
      ON   p.category_id = c.category_id
     GROUP BY
           c.category_id
     WITH UR 
    ;

    (2) Generally speaking, correlated-subquery show worse performance than uncorrelated-subquery or join.
    craigmc's subquery is a correlated-subquery.
    So, if performance was critical, it would be better to try both of Manidba49's way and craigmc's way and to compare their performance.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually, wouldn't the explain on Craig's and Mani's SQL statements be pretty much identical after going through query rewrite? I don't think it would be a correlated-subquery. From my understanding putting the sub-query within the select list as Craig did, is rewritten by DB2 into a left outer join, the same happens when you write a right outer join.

    Also, rather than the difficult to read case statement for when you might get a null on your count(*), why not just use a simple to understand VALUE clause?
    Code:
    SELECT c.category_id
         , value(COUNT(p.product_id),0) AS product_count
    I've always preferred the value clause over any other method of putting a default out there. You can use as many columns as you like, can always finish with a written default as above with the 0.

    example of multi-column if you had other tables or columns to see.
    Code:
    SELECT c.category_id
         , value(COUNT(p.product_id),COUNT(h.product_id),COUNT(k.product_id),0) AS product_count
         , value(sum(p.columnx), 0.00)
    ....
    Dave

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Subqueries might be rewritten to joins by DB2.
    But, the rewritten query might be not a query you expected.


    Here are some examples(similar to the queries previously provided, and more) using other tables(department and employee) and columns.

    Summary:
    Code:
    Query     | Presented by | Descriptions             | Access Plan |
    ----------+--------------+--------------------------+-------------+
    Example 1 | Craig        | scalar-subselect         | NLJOIN      |
    Example 2 | Tonkuma      | correlated-subquery      | NLJOIN      |
    Example 3 | Mani, Tonkm  | outer join ---> group by | HSJOIN      |
    Example 4 | Tonkuma      | group by ---> outer join | MSJOIN      |
    Example 1: similar to Craig's.
    Note: I inserted/updated some rows and added some constraints/indexes to original sample tables.
    So, you may see other Access Plan in your environment.
    Code:
    Original Statement:
    ------------------
    SELECT d.deptno ,
       (SELECT COUNT(*)
       FROM employee AS e
       WHERE e.workdept = d.deptno) AS employee_count
    FROM department AS d
    ORDER BY d.deptno
    
    
    Optimized Statement:
    -------------------
    SELECT Q4.DEPTNO AS "DEPTNO", Q3.$C0 AS "EMPLOYEE_COUNT"
    FROM
       (SELECT COUNT(*)
       FROM
          (SELECT $RID$
          FROM DB2ADMIN.EMPLOYEE AS Q1
          WHERE (Q1.WORKDEPT = Q4.DEPTNO)) AS Q2) AS Q3, DB2ADMIN.DEPARTMENT AS
            Q4
    ORDER BY Q4.DEPTNO
    Code:
    Access Plan:
    -----------
            Total Cost:             8.24118
            Query Degree:           1
    
                   Rows
                  RETURN
                  (   1)
                   Cost
                    I/O
                    |
                     9
                  NLJOIN
                  (   2)
                  8.24118
                     1
               /----+----\
              9             1
           IXSCAN        GRPBY
           (   3)        (   4)
          0.0392851      7.66255
              0             1
             |             |
              9          4.66667
       INDEX: SYSIBM     TBSCAN
     SQL111226160039830  (   5)
             Q4          7.66155
                            1
                           |
                           42
                     TABLE: DB2ADMIN
                        EMPLOYEE
                           Q1
    The Optimized Statement of Example 1 was same as Optimized Statement of the following Example 2.

    Example 2: Join with correlated-subquery.
    Code:
    Original Statement:
    ------------------
    SELECT d.deptno , e.employee_count
    FROM department AS d , LATERAL
       (SELECT COUNT(*) AS employee_count
       FROM employee AS e
       WHERE e.workdept = d.deptno) AS e
    ORDER BY d.deptno
    
    
    Optimized Statement:
    -------------------
    SELECT Q4.DEPTNO AS "DEPTNO", Q3.$C0 AS "EMPLOYEE_COUNT"
    FROM
       (SELECT COUNT(*)
       FROM
          (SELECT $RID$
          FROM DB2ADMIN.EMPLOYEE AS Q1
          WHERE (Q1.WORKDEPT = Q4.DEPTNO)) AS Q2) AS Q3, DB2ADMIN.DEPARTMENT AS
            Q4
    ORDER BY Q4.DEPTNO
    Code:
    Access Plan:
    -----------
            Total Cost:             8.24118
            Query Degree:           1
    
                   Rows
                  RETURN
                  (   1)
                   Cost
                    I/O
                    |
                     9
                  NLJOIN
                  (   2)
                  8.24118
                     1
               /----+----\
              9             1
           IXSCAN        GRPBY
           (   3)        (   4)
          0.0392851      7.66255
              0             1
             |             |
              9          4.66667
       INDEX: SYSIBM     TBSCAN
     SQL111226160039830  (   5)
             Q4          7.66155
                            1
                           |
                           42
                     TABLE: DB2ADMIN
                        EMPLOYEE
                           Q1

    Example 3: Using LEFT OUTER JOIN, then GROUP BY.(Originated by Mani, revised by me)
    Code:
    Original Statement:
    ------------------
    SELECT d.deptno , COUNT(e.empno) AS employee_count
    FROM department AS d LEFT OUTER JOIN employee AS e ON e.workdept = d.deptno
    GROUP BY d.deptno
    ORDER BY d.deptno
    
    
    Optimized Statement:
    -------------------
    SELECT Q4.DEPTNO AS "DEPTNO", Q4.$C1 AS "EMPLOYEE_COUNT"
    FROM
       (SELECT Q3.DEPTNO, COUNT(Q3.EMPNO)
       FROM
          (SELECT Q2.DEPTNO, Q1.EMPNO
          FROM DB2ADMIN.EMPLOYEE AS Q1 RIGHT OUTER JOIN DB2ADMIN.DEPARTMENT AS Q2
                  ON (Q1.WORKDEPT = Q2.DEPTNO)) AS Q3
       GROUP BY Q3.DEPTNO) AS Q4
    ORDER BY Q4.DEPTNO
    Code:
    Access Plan:
    -----------
            Total Cost:             7.72989
            Query Degree:           1
    
                   Rows
                  RETURN
                  (   1)
                   Cost
                    I/O
                    |
                     9
                  GRPBY
                  (   2)
                  7.72674
                     1
                    |
                     9
                  TBSCAN
                  (   3)
                  7.72497
                     1
                    |
                     9
                  SORT
                  (   4)
                  7.72228
                     1
                    |
                    42
                  HSJOIN<
                  (   5)
                  7.6949
                     1
             /------+-------\
           42                  9
         TBSCAN             IXSCAN
         (   6)             (   7)
         7.64831           0.0392851
            1                  0
           |                  |
           42                  9
     TABLE: DB2ADMIN    INDEX: SYSIBM
        EMPLOYEE      SQL111226160039830
           Q1                 Q2

    Example 4: GROUP BY in subquery, then LEFT OUTER JOIN.
    Note: VALUE(mentioned by Dave) is a synonym for COALESCE.
    Code:
    Original Statement:
    ------------------
    SELECT d.deptno , COALESCE(employee_count , 0) AS employee_count
    FROM department AS d LEFT OUTER JOIN
       (SELECT workdept , COUNT(*) AS employee_count
       FROM employee
       GROUP BY workdept) AS e ON e.workdept = d.deptno
    ORDER BY d.deptno
    
    
    Optimized Statement:
    -------------------
    SELECT Q5.DEPTNO AS "DEPTNO", COALESCE(2, Q4.$C1) AS "EMPLOYEE_COUNT"
    FROM
       (SELECT Q3.WORKDEPT, Q3.$C1
       FROM
          (SELECT Q2.WORKDEPT, COUNT(*)
          FROM
             (SELECT Q1.WORKDEPT
             FROM DB2ADMIN.EMPLOYEE AS Q1) AS Q2
          GROUP BY Q2.WORKDEPT) AS Q3) AS Q4 RIGHT OUTER JOIN DB2ADMIN.DEPARTMENT
            AS Q5 ON (Q4.WORKDEPT = Q5.DEPTNO)
    ORDER BY Q5.DEPTNO
    Code:
    Access Plan:
    -----------
            Total Cost:             7.73127
            Query Degree:           1
    
                   Rows
                  RETURN
                  (   1)
                   Cost
                    I/O
                    |
                     9
                  >MSJOIN
                  (   2)
                  7.72812
                     1
               /----+----\
              9         0.888889
           IXSCAN        FILTER
           (   3)        (   4)
          0.0392851      7.68348
              0             1
             |             |
              9             8
       INDEX: SYSIBM     GRPBY
     SQL111226160039830  (   5)
             Q5          7.6778
                            1
                           |
                            8
                         TBSCAN
                         (   6)
                         7.67621
                            1
                           |
                            8
                         SORT
                         (   7)
                         7.6737
                            1
                           |
                           42
                         TBSCAN
                         (   8)
                         7.64831
                            1
                           |
                           42
                     TABLE: DB2ADMIN
                        EMPLOYEE
                           Q1
    Last edited by tonkuma; 10-09-14 at 16:09. Reason: Add Summary:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •