Need help with COUNT() function 1

[deeciple]

Hi Everyone,

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

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

 

[jpadie]

Remove the group by clause.

 

[SkipVought]

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

[b]GROUP BY tblequipissues.EquipType, tblequipissues.Issue, tblequipissues.IssueDate, tblequipissues.FixedDate, tblequipissues.EnteredBy[/b]

ORDER BY tblequipissues.IssueDate

 

[deeciple]

Hi All,

Thanks for your replies. When I remove the GROUP BY clause I get:

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

If I group by the other fields as well, I get:

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

I know I need to do a join somehow (I used to do this kind of thing in MS Access all the time) but I don't know how to do it with pure SQL.

Thanks,

Ken

 

[jpadie]

Not sure why you would want to return an aggregate figure on each line item. That sounds odd

But if you must the easiest way might be a join to a copy of the table. Then perform a count on that join.

 

[deeciple]

I got it working. MS Access lets you construct queries using a gui then you can switch to the SQL view. It needed some tweaking afterward but here is the query as it is in my PHP page:

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 needed to do it this way so I could loop through the records and create a master and detail level for each "EnteredBy" group. I want to make the details level collapsible/expandable, something like:

"Susan has submitted ".row_rstEquipIssues['CountOfEnteredBy']." tickets this period.  Click to expand and see the details.";

 

[jpadie]

run this query against a large dataset. and then run it again as multiple separate queries. you may be surprised. Running separate queries is not always inefficient.

by the by, the output from access looks very inefficient.

 

[deeciple]

How would you make this query more efficient and still achieve the same output?

I am not very experienced with SQL so unfortunately I have to use a gui like Access. I agree, like most front ends the code that it generates is not very efficient.

 

[jpadie]

run your loop sensibly.

$data =array();
while($row = mysql_fetch_assoc($result)):
 if(!isset($data[$row['EnteredBy']]) $data[$row['EnteredBy']] = array();
 $data[$row['EnteredBy']][] = $row;
endwhile;

then you implicitly know how many events relate to a user. just run a count($data['John']); to get the answer when you want it. or even easier perhaps.

$data =array();
while($row = mysql_fetch_assoc($result)):
 $data[$row['EnteredBy']]++;
 $rows[] = $row;
endwhile;

then you just need

echo $data['John'];

to get John's count. But then the array is not nicely organised into an array. If you are concerned about the 'cost' of running a count then this method is 'cheaper'.

 

[ChrisHirst]

I am not very experienced with SQL so unfortunately I have to use a gui like Access.

HeidiSQL

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[deeciple]

run your loop sensibly.

$data =array();
while($row = mysql_fetch_assoc($result)):
 if(!isset($data[$row['EnteredBy']]) $data[$row['EnteredBy']] = array();
 $data[$row['EnteredBy']][] = $row;
endwhile;

then you implicitly know how many events relate to a user. just run a count($data['John']); to get the answer when you want it. or even easier perhaps.

$data =array();
while($row = mysql_fetch_assoc($result)):
 $data[$row['EnteredBy']]++;
 $rows[] = $row;
endwhile;

then you just need

echo $data['John'];

to get John's count. But then the array is not nicely organised into an array. If you are concerned about the 'cost' of running a count then this method is 'cheaper'.

That's interesting. I tried looping through the records in a similar fashion but the issue I ran into is I don't know in advance which users' records were going to be returned by the query? How would the counter be able to know when one user's records ended and another user's records began?

 

[jpadie]

try it!
it doesn't need to know the start and end because in the first it reorders the array so that it is stored by user; and in the second it keeps the order intact and just creates a counter per user.

 

[deeciple]

I'll give it a try when I get into the office tomorrow. Thanks!