Want a count but it repeats 1 with every record

Question

I want a count but it repeats 1 with every record. Can you please suggest what to do?

SELECT Count(*),
       innerTable.*
FROM   (SELECT (SELECT NAME
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS NAME,
               (SELECT url
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS Url,
               (SELECT TOP 1 NAME
                FROM   tourimages
                WHERE  tourimages.tourid = tourbooking.tourid
                ORDER  BY id ASC)                             AS ImageName,
               (SELECT duration + ' ' + CASE WHEN durationtype = 'd' THEN
                       'Day(s)' WHEN
                       durationtype =
                       'h' THEN 'Hour(s)' END
                FROM   tours
                WHERE  tours.id = tourbooking.tourid)         AS Duration,
               (SELECT Replace(Replace('<a> Adult(s) - <c> Children', '<a>', Sum
                               (CASE
                                       WHEN [type] = 1 THEN 1
                                       ELSE 0
                                END)),
                       '<c>',
                       Sum(CASE
                       WHEN [type] = 2 THEN 1
                       ELSE 0
                       END))
                FROM   tourperson
                WHERE  tourperson.bookingid = tourbooking.id) AS TotalPassengers
               ,
               startdate,
               createddate                                    AS BookingDate,
               id                                             AS BookingID,
               [status],
               serviceprice
        FROM   tourbooking
        WHERE  memberid = 6)AS innerTable
GROUP  BY innerTable.NAME,
          innerTable.bookingdate,
          innerTable.bookingid,
          innerTable.duration,
          innerTable.imagename,
          innerTable.serviceprice,
          innerTable.startdate,
          innerTable.status,
          innerTable.totalpassengers,
          innerTable.url

This is sounds like [xy problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Please give us your tables with data and what do you expect and not your approach to achieve it. — Mahesh, Feb 10 '15 at 07:17
Select Count(*),T.* From (SELECT Tours.Name,Tours.Duration,Tours.DurationType,Tours.Url,TourImages.Name as ImageName,TourBooking.CreatedDate as BookingDate, TourBooking.ID as BookingID, TourBooking.StartDate as StartDate,TourBooking.ServicePrice as ServicePrice from TourBooking join Tours on TourBooking.TourID = Tours.ID join TourImages on TourImages.TourID = TourBooking.TourID Where TourBooking.MemberID = 6) as T Group by T.BookingDate, t.BookingID, t.Duration, t.DurationType, t.ImageName, t.Name, t.ServicePrice, t.StartDate, t.Url — Mike, Feb 10 '15 at 07:45
@Coder of Code please check it ,, it is through join and i just want a count and result — Mike, Feb 10 '15 at 07:46
Post your new query as update in question and did you read @Throsten answer. It explains the probable cause of the problem. — Mahesh, Feb 10 '15 at 07:49

score 0 · Accepted Answer · answered Feb 10 '15 at 07:36

0

You select records from tourbooking. One of the columns you select is id. This is probably the table's primary key and thus unique. (If not, you should hurry to change that name.)

You call this ID BookingID, and it is one of the columns you group by. So you get one result record per record in tourbooking. The number of records within such a "group" is of course 1; it is the one record you select and show.

If you built real groups, say a result record per day, then you'd get a real count, e.g. the number of bookings per day.

answered Feb 10 '15 at 07:36

Thorsten Kettner

89,309
7
49
73

yes you are right you got the point .. can you post how is that possible ... BookingID is Primary key – Mike Feb 10 '15 at 07:56
In your GROUP BY clause you would specify what records you want to see in your results. An example: `GROUP BY innerTable.bookingdate, innerTable.status` would give you *one* result record *per* bookingdate and status. In your SELECT clause, you would not select `innerTable.*`, but aggregates for a group, e.g.: `innerTable.bookingdate, innerTable.status, MIN(innerTable.duration) AS min_duration, MAX(innerTable.duration) AS max_duration, SUM(innerTable.totalpassengers) AS passengers, COUNT(*) AS bookings`. So: think about what you want to group by and think about what data to show per group. – Thorsten Kettner Feb 10 '15 at 08:12
please visit this ..... https://stackoverflow.com/questions/28428157/how-to-get-count-with – Mike Feb 10 '15 at 09:29

Want a count but it repeats 1 with every record

1 Answers1