1 Reply Latest reply on Jun 17, 2010 2:25 AM by Adam Cameron.

    How To Use QoQ For Group By Count

    gregmoser

      Ok, I am having a issue with Group By and Count.  Basically, here is what i am working with...

       

      <cfquery name="TEST" datasource="MyDataSource">

           SELECT

                ContentID, ContentName, ProductID

           FROM

                MyTable

      </cfquery>

       

      HERE IS AN EXAMPLE DUMP

       

      1, ABCD, 33

      1, ABCD, 34

      1, ABCD, 35

      1, ABCD, 36

      1, ABCD, 37

      2, EFG, 342

      2, EFG, 343

      2, EFG, 344

      2, EFG, 345

      2, EFG, 346

      2, EFG, 347

      2, EFG, 348

      3, HIJK, 101

      3, HIJK, 102

      3, HIJK, 103

       

      Then I run this query on it.

       

      <cfquery name="MYGROUPLIS" dbtype="query">

            SELECT

                ContentID, ContentName, COUNT(ProductID)

            FROM

                TEST

           GROUP BY

                ContentID, ContentName

      </cfquery>

       

      It Should be this:

       

      1, ABCD, 5

      2, EFG, 7

      3, HIJK, 3

       

       

      But instead i get this:

      1, ABCD, 12

      1, EFJ, 12

      1, HIJK, 12

       

       

      WHY?????

        • 1. Re: How To Use QoQ For Group By Count
          Adam Cameron. Level 5

          I refactored your example code to be stand-alone, and ran it.

           

          Here's the code:

           

          <cfscript>
              qTest = queryNew("contentId,contentName,ProductID", "Integer,Varchar,Integer");
              queryAddRow(qTest); querySetCell(qTest, "contentId", 1); querySetCell(qTest, "contentName", "ABCD"); querySetCell(qTest, "ProductID", 33);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 1); querySetCell(qTest, "contentName", "ABCD"); querySetCell(qTest, "ProductID", 34);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 1); querySetCell(qTest, "contentName", "ABCD"); querySetCell(qTest, "ProductID", 35);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 1); querySetCell(qTest, "contentName", "ABCD"); querySetCell(qTest, "ProductID", 36);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 1); querySetCell(qTest, "contentName", "ABCD"); querySetCell(qTest, "ProductID", 37);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 342);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 343);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 344);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 345);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 346);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 347);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 2); querySetCell(qTest, "contentName", "EFG"); querySetCell(qTest, "ProductID", 348);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 3); querySetCell(qTest, "contentName", "HIJK"); querySetCell(qTest, "ProductID", 101);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 3); querySetCell(qTest, "contentName", "HIJK"); querySetCell(qTest, "ProductID", 102);
              queryAddRow(qTest); querySetCell(qTest, "contentId", 3); querySetCell(qTest, "contentName", "HIJK"); querySetCell(qTest, "ProductID", 103);
          </cfscript>


          <cfquery name="qGrouped" dbtype="query">
              SELECT        contentId, contentName, COUNT(ProductID)
              FROM        qTest
              GROUP BY    contentId, contentName
          </cfquery>
          <cfdump var="#qGrouped#">

           

          Here's the result:

           

          COLUMN_2CONTENTIDCONTENTNAME
          151ABCD
          272EFG
          333HIJK

           

          Which is what of us expect.

           

          What version of CF are you on?  I'm running this on CF8.0.1

           

          Are you certain about the data coming back from the DB?

           

          --

          Adam