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.