INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need help with COUNT() function

Need help with COUNT() function

(OP)
Hi Everyone,

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

CODE --> SQL

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

RE: Need help with COUNT() function

Remove the group by clause.

RE: Need help with COUNT() function

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.EquipType, tblequipissues.Issue, tblequipissues.IssueDate, tblequipissues.FixedDate, tblequipissues.EnteredBy

ORDER BY tblequipissues.IssueDate 

RE: Need help with COUNT() function

(OP)
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

RE: Need help with COUNT() function

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.

RE: Need help with COUNT() function

(OP)
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:

CODE --> PHP

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:

CODE --> PHP

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

RE: Need help with COUNT() function

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.

RE: Need help with COUNT() function

(OP)
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.

RE: Need help with COUNT() function

run your loop sensibly.

CODE

$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.

CODE

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

then you just need

CODE

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'.

RE: Need help with COUNT() function

Quote:

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

[link http://www.heidisql.com/]HeidiSQL[/url]

Chris.

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

RE: Need help with COUNT() function

(OP)

Quote (jpadie)

run your loop sensibly.

CODE --> PHP

$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.

CODE --> PHP

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

then you just need

CODE --> PHP

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?

RE: Need help with COUNT() function

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.

RE: Need help with COUNT() function

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close