Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts

    Need help with COUNT()

    Hi Everyone,

    I have this query to count how many support requests are made within a given date range:

    Code:
    SELECT tblequipissues.EquipType, tblequipissues.Issue, tblequipissues.IssueDate, tblequipissues.FixedDate, tblequipissues.EnteredBy, COUNT(tblequipissues.EnteredBy)
    FROM tblequipissues
    WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    GROUP BY tblequipissues.EnteredBy
    ORDER BY tblequipissues.IssueDate
    I was hoping to get the data returned back something like follows:

    VTR | Broken | 6/14 | 6/15 | Larry | 3
    TV | Gone | 6/17 | 6/18 | Larry | 3
    Spkr| Blown | 6/20 | 6/21 | Larry | 3
    Mic | Broken | 6/15 | 6/16 | Tom | 1
    Mic | Broken | 6/14 | 6/16 | Sarah | 2
    Spkr| Blown | 6/15 | 6/16 | Sarah | 2

    But what I am getting is more like:

    VTR | Broken | 6/14 | 6/15 | Larry | 3
    Mic | Broken | 6/15 | 6/16 | Tom | 1
    Mic | Broken | 6/14 | 6/16 | Sarah | 2


    I want to be able to loop through the records to create a master and detail levels in a report page I am working on. Is there a way I can get my records returned like the first example?

    Thanks in advance,

    Ken

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,307
    Thanks
    4
    Thanked 205 Times in 202 Posts
    Wrong information removed
    Last edited by djm0219; 07-19-2014 at 09:49 AM.
    Dave .... HostMonster for all of your hosting needs

  • #3
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Hi Dave,

    Thanks for your reply. I had tried that but I got a count of all the records returned:

    VTR | Broken | 6/14 | 6/15 | Larry | 6
    TV | Gone | 6/17 | 6/18 | Larry | 6
    Spkr| Blown | 6/20 | 6/21 | Larry | 6
    Mic | Broken | 6/15 | 6/16 | Tom | 6
    Mic | Broken | 6/14 | 6/16 | Sarah | 6
    Spkr| Blown | 6/15 | 6/16 | Sarah | 6

    I will try again though to see if maybe I did something wrong the first time. In MS Access I would create a query without the COUNT() or GROUP BY and then join this to another query containing both the COUNT() and GROUP BY. I don't know if this is the correct method but the join forces ALL the records to be returned as I wanted. I don't know how to do that using pure SQL, as Access provides a gui for creating queries

    Kind regards,

    Ken

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Quote Originally Posted by djm0219 View Post
    Off the top of my head remove the GROUP BY tblequipissues.EnteredBy in your query.
    Completely wrong answer. In fact, you want to do the *OPPOSITE*.

    Properly, when you use an aggregate function (COUNT, MIN, SUM, etc.), you should GROUP BY *ALL* of the non-aggregate fields!

    So:
    Code:
    SELECT EquipType, Issue, IssueDate, FixedDate, EnteredBy, COUNT(EnteredBy) AS theCount
    FROM tblequipissues
    WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    GROUP BY EquipType, Issue, IssueDate, FixedDate, EnteredBy
    ORDER BY IssueDate
    Note that there is never any reason to repeat the TABLE NAME in a query when only one table is involved.
    Doesn't hurt, but it's a waste of coding time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    OK so I was able to pull this off in Access using a sample of my data that I imported. It took three separate queries and I really don't think it needs to be this complicated.

    Query 1:
    Code:
    SELECT tblEquipIssues.EnteredBy, Count(tblEquipIssues.EnteredBy) AS CountOfEnteredBy
    FROM tblEquipIssues
    WHERE (((tblEquipIssues.IssueDate) Between [start] And [end]) AND ((tblEquipIssues.SendTo) Like [SentTo]))
    GROUP BY tblEquipIssues.EnteredBy
    ORDER BY tblEquipIssues.EnteredBy;
    Query 2:
    Code:
    SELECT tblEquipIssues.IssueDate, tblEquipIssues.EnteredBy, tblEquipIssues.EquipType, tblEquipIssues.Issue, tblEquipIssues.FixedDate
    FROM tblEquipIssues
    WHERE (((tblEquipIssues.IssueDate) Between [start] And [end]) AND ((tblEquipIssues.SendTo) Like [SentTo]))
    ORDER BY tblEquipIssues.EnteredBy;
    Query 3:
    Code:
    SELECT Query2.IssueDate, Query2.EnteredBy, Query1.CountOfEnteredBy, Query2.EquipType, Query2.Issue, Query2.FixedDate
    FROM Query1 INNER JOIN Query2 ON Query1.EnteredBy = Query2.EnteredBy
    ORDER BY Query2.EnteredBy;
    Problem is I am using Dreamweaver and I, for the life of me, don't have a clue as to how to reproduce this there, or in any actual SQL code.



    UPDATE:
    Old Pedant,

    I saw your reply after my last post. I hadn't thought about that and I am trying your idea now.......
    Last edited by MaDmiX; 07-18-2014 at 08:12 PM.

  • #6
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Just tried it but the results came back like this:

    VTR | Broken | 6/14 | 6/15 | Larry | 1
    TV | Gone | 6/17 | 6/18 | Larry | 1
    Spkr| Blown | 6/20 | 6/21 | Larry | 1
    Mic | Broken | 6/15 | 6/16 | Tom | 1
    Mic | Broken | 6/14 | 6/16 | Sarah | 1
    Spkr| Blown | 6/15 | 6/16 | Sarah | 1

    Looks like the count function counts each record returned as 1 when you do this. If I had any hair I'd be pulling it out by now, lol.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Okay, so then the problem is that you are SELECTing *too many* fields.

    You should ONLY SELECT the fields that will be the same when getting the counts.

    Now, you said you wanted "to create a master and detail levels", so it looks to me like you already know the answer:
    Just convert the Access query to MySQL.

    Thus:
    Code:
    SELECT Query2.IssueDate, Query2.EnteredBy, Query1.CountOfEnteredBy, Query2.EquipType, Query2.Issue, Query2.FixedDate
    FROM
         ( SELECT EnteredBy, Count(EnteredBy) AS CountOfEnteredBy
           FROM tblEquipIssues
           WHERE (((IssueDate) Between [start] And [end]) AND ((SendTo) Like [SentTo]))
           GROUP BY EnteredBy ) AS Query1
    INNER JOIN 
         ( SELECT IssueDate, EnteredBy, EquipType, Issue, FixedDate
           FROM tblEquipIssues
           WHERE (((IssueDate) Between [start] And [end]) AND ((SendTo) Like [SentTo]))
         ) AS Query2
    ON Query1.EnteredBy = Query2.EnteredBy
    ORDER BY Query2.EnteredBy;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    MaDmiX (07-20-2014)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Or we can simplify that somewhat, since Query2 is actually simply getting specific fields from the table.

    Thus:
    Code:
    SELECT Issues.IssueDate, Issues.EnteredBy, counts.CountOfEnteredBy, Issues.EquipType, Issues.Issue, Issues.FixedDate
    FROM tblEquipIssues AS Issues
    INNER JOIN
         ( SELECT EnteredBy, Count(EnteredBy) AS CountOfEnteredBy
           FROM tblEquipIssues
           WHERE (((IssueDate) Between [start] And [end]) AND ((SendTo) Like [SentTo]))
           GROUP BY EnteredBy ) AS counts
    ON counts.EnteredBy = Issues.EnteredBy
    ORDER BY Issues.EnteredBy;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    MaDmiX (07-18-2014)

  • #9
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    OK I've been at this for quite a while now and I am beginning to dislike Dreamweaver very much. It is not happy with the SQL no matter how I structure it. I have managed to get it to like the following but it complains when I try to join the two.

    query 1:

    Code:
    SELECT EnteredBy, COUNT(EnteredBy) AS CountOfEnteredBy
    FROM tblEquipIssues
    WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    GROUP BY EnteredBy
    query 2:

    Code:
    SELECT IssueDate, EnteredBy, EquipType, Issue, FixedDate
    FROM tblEquipIssues
    WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    BTW, I was not aware you could specify the queries in one statement like that. Thanks!
    I abandoned trying to get the query to work in Dreamweaver and I know this isn't a PHP forum but for what it's worth, this is how I have it coded in my PHP script:

    Code:
    if(isset($_POST['ReportTitle'])) {$ReportTitle = $_POST['ReportTitle'];}
    if(isset($_POST['Interval'])) {$Interval = $_POST['Interval'];}
    if(isset($_POST['SentTo'])) {$SentTo = $_POST['SentTo'];}
    if(isset($_POST['StartDate'])) {$StartDate = $_POST['StartDate'];}
    if(isset($_POST['EndDate'])) {$EndDate = $_POST['Enddate'];}
    
    $rstEquipIssues = mysql_query("SELECT Query2.IssueDate, Query2.EnteredBy, Query1.CountOfEnteredBy, Query2.EquipType, Query2.Issue, Query2.FixedDate
    							FROM
    								(SELECT `EnteredBy`, COUNT(`EnteredBy`) AS CountOfEnteredBy
    								FROM tblEquipIssues
    								WHERE IssueDate >= '$StartDate' AND  IssueDate <= '$EndDate' AND SendTo LIKE '$SentTo'
    								GROUP BY EnteredBy) AS Query1
    							INNER JOIN 
    								(SELECT IssueDate, EnteredBy, EquipType, Issue, FixedDate
    								FROM tblEquipIssues
    								WHERE IssueDate >= '$StartDate' AND  IssueDate <= '$EndDate' AND SendTo LIKE '$SentTo') AS Query2
    							ON Query1.EnteredBy = Query2.EnteredBy
    							ORDER BY Query2.EnteredBy");
    $row_rstEquipIssues = mysql_fetch_assoc($rstEquipIssues);
    $totalRows_rstEquipIssues = mysql_num_rows($rstEquipIssues);
    Last edited by MaDmiX; 07-18-2014 at 10:30 PM.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    What can I say? Stop using Dreamweaver and use something like Notepad+ or even Notepad.

    I don't understand why you would be using DoofusWhacker to construct queries directly, in any case.

    For what it's worth, the equivalent of a constructed query in Access is the VIEW in most any other database.

    So you *COULD* do:
    Code:
    CREATE VIEW query1 AS
        SELECT EnteredBy, COUNT(EnteredBy) AS CountOfEnteredBy
        FROM tblEquipIssues
        WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
        GROUP BY EnteredBy;
    
    CREATE VIEW query2 AS
        SELECT IssueDate, EnteredBy, EquipType, Issue, FixedDate
        FROM tblEquipIssues
        WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    
    CREATE VIEW query3 AS
        SELECT Query2.IssueDate, Query2.EnteredBy, Query1.CountOfEnteredBy, Query2.EquipType, Query2.Issue, Query2.FixedDate
        FROM Query1 INNER JOIN Query2
        ON Query1.EnteredBy = Query2.EnteredBy
    DO NOT USE DrunkWalker to do this. Use a MySQL query tool, such as MyPhpAdmin or whatever.

    Now in your PHP code you can just do
    Code:
    $sql = "SELECT * FROM query3 ORDER BY enteredBy";
    I still don't understand why DingleWhomper won't let you simply code PHP as
    Code:
    $sql = 
        "SELECT Issues.IssueDate, Issues.EnteredBy, counts.CountOfEnteredBy, Issues.EquipType, Issues.Issue, Issues.FixedDate
         FROM tblEquipIssues AS Issues
         INNER JOIN
             ( SELECT EnteredBy, Count(EnteredBy) AS CountOfEnteredBy
               FROM tblEquipIssues
               WHERE (((IssueDate) Between [start] And [end]) AND ((SendTo) Like [SentTo]))
               GROUP BY EnteredBy ) AS counts
         ON counts.EnteredBy = Issues.EnteredBy
         ORDER BY Issues.EnteredBy";
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    I agree. Dreamweaver makes some things simpler but other things are near IMPOSSIBLE with it. I use a combination and for the most part I use Sublime Text as my text editor for coding. I have to laugh because I just noticed that every time I make a change to my query using DW it just slaps another copy of the code below the previous one... sloooopy.

    I think I will abandon altogether the idea of trying to get my query to return these records just so and do a simpler one like:

    Code:
    SELECT EquipType, Issue, IssueDate, FixedDate, EnteredBy
    FROM tblequipissues
    WHERE IssueDate >= StartDate AND  IssueDate <= EndDate AND SendTo LIKE SentTo
    ORDER BY EnteredBy, IssueDate
    Then maybe I can use a foreach loop in php to get my count somehow.

    Thanks for all your help
    Last edited by MaDmiX; 07-18-2014 at 11:13 PM.

  • #12
    Regular Coder MaDmiX's Avatar
    Join Date
    Feb 2012
    Location
    Charlotte, NC
    Posts
    199
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Hi Old Pedant,

    Success!!!

    So I was testing this page on my production server (only because my test server doesn't have enough records to do a decent query with... I really need to import the live database to my test server ). Anyhoo, once I turned on error reporting I was able to see where the problems in the code were and I quickly had the query working... YAY!!!

    PHP Code:
    if(isset($_POST['ReportTitle'])) {$ReportTitle $_POST['ReportTitle'];}
    if(isset(
    $_POST['Interval'])) {$Interval $_POST['Interval'];}
    if (isset(
    $_POST['StartDate'])) {$StartDate $_POST['StartDate'];}
    if (isset(
    $_POST['EndDate'])) {$EndDate $_POST['EndDate'];}
    if (isset(
    $_POST['SentTo'])) {$SentTo $_POST['SentTo'];}

    mysql_select_db($database_connEngSupport$connEngSupport);
    $query_rstEquipIssues "SELECT Query2.IssueDate, Query2.EnteredBy, Query1.CountOfEnteredBy, Query2.EquipType, Query2.Issue, Query2.FixedDate
    FROM 
    (SELECT tblEquipIssues.EnteredBy, Count(tblEquipIssues.EnteredBy) AS CountOfEnteredBy
    FROM tblEquipIssues
    WHERE (((tblEquipIssues.IssueDate) Between '$StartDate' And '$EndDate') AND ((tblEquipIssues.SendTo) Like '$SentTo'))
    GROUP BY tblEquipIssues.EnteredBy
    ORDER BY tblEquipIssues.EnteredBy) AS Query1
    INNER JOIN
    (SELECT tblEquipIssues.IssueDate, tblEquipIssues.EnteredBy, tblEquipIssues.EquipType, tblEquipIssues.Issue, tblEquipIssues.FixedDate
    FROM tblEquipIssues
    WHERE (((tblEquipIssues.IssueDate) Between '$StartDate' And '$EndDate') AND ((tblEquipIssues.SendTo) Like '$SentTo'))
    ORDER BY tblEquipIssues.EnteredBy) AS Query2
    ON Query1.EnteredBy = Query2.EnteredBy
    ORDER BY Query2.EnteredBy;"
    ;
    $rstEquipIssues mysql_query($query_rstEquipIssues$connEngSupport) or die(mysql_error());
    $row_rstEquipIssues mysql_fetch_assoc($rstEquipIssues);
    $totalRows_rstEquipIssues mysql_num_rows($rstEquipIssues); 
    I will probably try using your more simplified version of the query at some point but for now I am just happy it is working the way I want. Thanks a million for your help

    Best regards,

    Ken


  •  

    Posting Permissions

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