We help IT Professionals succeed at work.

SQL Expression Fields

ChristianHaynes
on
Hi All,

I am trying to create a sql expression field but I keep getting a syntax error on the select statement, code below:

SELECT COUNT(Booking."PersonID") From Booking INNER JOIN Event ON Booking."EventID" = Event."EventID"

Any ideas?

Otherwise, is there any other way around this, below is what I am trying to achieve:

I am using 2 sql tables - Event and Bookings. There are many bookings to an event, I am currently listing in the report all the available events, but for each record I
want to show how many bookings have been made on each event and the way this is done is by counting each booking record in the booking table which has the eventID of the event.. Applying this in SQL is simple:

SELECT COUNT(*) From Booking INNER JOIN Event ON
Booking.EventID = Event.EventID
GROUP BY EVENTID

I just can't seem to apply it to Crystal. I have Crystal Reports 7.

Please help.

Thanks.

Christian
 
Comment
Watch Question

You can create a dummy formula field say @cnt and set to 1, in your report. Now create a summary on this field group by Event. Hope this would help you.

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
You need to do something like this

SELECT COUNT(Booking."PersonID") as Event_Count
FROM Booking INNER JOIN Event ON Booking."EventID" = Event."EventID"

Crystal will then have a field named Event_Count (you can choose your own name) to display in the report.

The problem may also be the "s around the field names.  We don't use them in our application but they may work for you.

good luck
mlmcc

Commented:
This question has been open for some time now.  Please return and either
1.  Accept an answer
2.  Post a comment stating that you need more information
3.  Ask Community Support to delete the question if you feel the information here has no value to you or anyone else.
4.  Ask Community Support to PAQ the question because your question was not answered but you feel the information here may be of some value to others.

Community Support is here:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt and you can leave a message by posting a zero point question.  Be sure to reference the question ID, found in the Address bar of your browser.

Thanks
DRRYAN3

Commented:
This question has been open now for months without a resolution and/or an accepted answer and will be submitted to the Cleanup section for closure.

My recommendation:  Points to mlmcc

DRRYAN3

Commented:
Force accepted as proposed

modulo

Community Support Moderator
Experts Exchange

Explore More ContentExplore courses, solutions, and other research materials related to this topic.