Solved

simple recordcount for output query

Posted on 2003-11-24
1
172 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 75 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now