Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

simple recordcount for output query

Posted on 2003-11-24
1
Medium Priority
?
194 Views
Last Modified: 2013-12-24
can't get this:  each of the following timeslots repeats 15 times but i'm only showing 1.  how can i show the record count for each unique repeating timeslot

eg:

February 6, 2004  8:30 AM (15 timeslots remain)
February 6, 2004  1:30 PM 6 (15 timeslots remain)

February 7, 2004  8:30 AM 6 (15 timeslots remain)

February 7, 2004  1:30 PM 6 (15 timeslots remain)

February 16, 2004  8:30 AM 6 (15 timeslots remain)

February 16, 2004  1:30 PM 6 (15 timeslots remain)


here's my code:

<cfquery name="rsTimes" datasource=#MM_connCalArts_DSN# username=#MM_connCalArts_USERNAME# password=#MM_connCalArts_PASSWORD#>
SELECT  timeslots_theater.timeslots, mix_theater.cities_id, cities_theater.cities,
mix_theater.timeslots_id, mix_theater.mix_id, timeslots_theater.id FROM timeslots_theater,
mix_theater, cities_theater WHERE timeslots_theater.id = mix_theater.timeslots_id
AND cities_theater.id = mix_theater.cities_id AND cities_id = '#rsTimes__MMColParam#'
group by timeslots
order by timeslots asc
</cfquery>

>>>>>>>>>>>>>>>>>>>>>>>>>>>

<cfoutput query="rsTimes" group="timeslots">
    <li><A HREF="application.cfm?#Trim(MM_keepURL & Iif(MM_keepURL NEQ '',DE('&'),DE('')) & 'mix_id=' & rsTimes.mix_id)#" class="craig">#dateformat(rsTimes.timeslots, 'mmmm d, yyyy')#&nbsp; #timeformat(rsTimes.timeslots, 'h:MM tt')#</A>
  </cfoutput>
0
Comment
Question by:phillystyle123
1 Comment
 
LVL 10

Accepted Solution

by:
Mause earned 300 total points
ID: 9810036
Try this:

<cfquery name="rsTimes" datasource=#MM_connCalArts_DSN# username=#MM_connCalArts_USERNAME# password=#MM_connCalArts_PASSWORD#>
      SELECT  count(*) as slotsremain,
                  timeslots_theater.timeslots,
                  mix_theater.cities_id,
                  cities_theater.cities,
                  mix_theater.timeslots_id,
                  mix_theater.mix_id,
                  timeslots_theater.id
      FROM      timeslots_theater, mix_theater, cities_theater
      WHERE      timeslots_theater.id = mix_theater.timeslots_id
      AND       cities_theater.id = mix_theater.cities_id
      AND       cities_id = '#rsTimes__MMColParam#'
      group by timeslots
      order by timeslots asc
</cfquery>

You can use #slotsremain# in your query-loop for the remaining slots!

let me know
Mause
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question