Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-41668

Count(Distinct) + Having ==> Empty Result

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 12.3.2
    • N/A
    • Optimizer
    • Windows 11
    • Not for Release Notes

    Description

      DROP TABLE if EXISTS WORK.MyTVShows ;
      CREATE TABLE WORK.MyTVShows (tconst CHAR(10)) ;
      INSERT INTO WORK.MyTVShows (tconst) VALUES ('tt0898266') ;

      DROP TABLE if EXISTS WORK.Episodes ;
      CREATE TABLE WORK.Episodes (tconst CHAR(10), parentTconst CHAR(10)) ;
      INSERT INTO WORK.Episodes (tconst, parentTconst) VALUES ('tt0775431', 'tt0898266'), ('tt1091289', 'tt0898266') ;

      DROP TABLE if EXISTS WORK.ActingRoles ;
      CREATE TABLE WORK.ActingRoles (tconst CHAR(10), nconst CHAR(10), voice CHAR(1)) ;
      INSERT INTO WORK.ActingRoles (tconst, nconst, voice) VALUES ('tt0775431', 'nm0192505', ' '), ('tt1091289', 'nm0192505', ' ') ;

      DROP TABLE if EXISTS WORK.Artists ;
      CREATE TABLE WORK.Artists (nconst CHAR(10), primaryName VARCHAR(128), gender CHAR(1)) ;
      INSERT INTO WORK.Artists (nconst, primaryName, gender) VALUES ('nm0192505', 'Kaley Cuoco', 'F') ;

      1. Distinct + Having ==> Empty Result

      SELECT d.nconst, d.primaryName,
      COUNT(b.tconst) AS nbrEpisode,
      COUNT(distinct b.parentTconst) AS nbrShow,
      RANK() OVER (ORDER BY nbrEpisode desc) as actRank
      FROM WORK.MyTVShows a,
      WORK.Episodes b,
      WORK.ActingRoles c,
      WORK.Artists d
      WHERE a.tconst = b.parentTconst
      AND b.tconst = c.tconst
      AND c.nconst = d.nconst
      AND c.voice NOT IN ("C", "D")
      AND d.gender = "F"
      GROUP BY d.nconst, d.primaryName
      HAVING nbrEpisode >= 1
      ORDER BY actRank, d.primaryName ;

      1. Distinct without Having ==> Works, but I want Having clause

      SELECT d.nconst, d.primaryName,
      COUNT(b.tconst) AS nbrEpisode,
      COUNT(distinct b.parentTconst) AS nbrShow,
      RANK() OVER (ORDER BY nbrEpisode desc) as actRank
      FROM WORK.MyTVShows a,
      WORK.Episodes b,
      WORK.ActingRoles c,
      WORK.Artists d
      WHERE a.tconst = b.parentTconst
      AND b.tconst = c.tconst
      AND c.nconst = d.nconst
      AND c.voice NOT IN ("C", "D")
      AND d.gender = "F"
      GROUP BY d.nconst, d.primaryName
      ORDER BY actRank, d.primaryName ;

      1. Having without Distinct ==> Works, but I need Distinct to obtain desired result

      SELECT d.nconst, d.primaryName,
      COUNT(b.tconst) AS nbrEpisode,
      COUNT(b.parentTconst) AS nbrShow,
      RANK() OVER (ORDER BY nbrEpisode desc) as actRank
      FROM WORK.MyTVShows a,
      WORK.Episodes b,
      WORK.ActingRoles c,
      WORK.Artists d
      WHERE a.tconst = b.parentTconst
      AND b.tconst = c.tconst
      AND c.nconst = d.nconst
      AND c.voice NOT IN ("C", "D")
      AND d.gender = "F"
      GROUP BY d.nconst, d.primaryName
      HAVING nbrEpisode >= 1
      ORDER BY actRank, d.primaryName ;

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              KokoBel Mario Van Hecke
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.