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