SQL Count of columns result for all existing Dates in the table -
i have table dttransaction , dthoteltransaction transactionid primary key in former , foreign key in later.
columns in dttransaction :machinename(varchar),bookedon(datetime),transactionid(int) columns in dthoteltransaction :hcomcid(int) ,transactionid
requirement :
for every bookedon value in dttransaction , want machine name , count of machinename these (415428,415429,415430,415431,415432) hcomcid.
i trying :
select dttransaction.machinename,count(dttransaction.machinename) dttransaction join dthotelreservation on dttransaction.transactionid = dthotelreservation.transactionid , dthotelreservation.hcomcid in ( 415428, 415429, 415430, 415431, 415432) convert(varchar(10),bookedon,101)='04/10/2013' group dttransaction.machinename
but give particular date.i want count existing bookedon values.
thanks in advance.
if want sum of dates, remove where
clause:
select dttransaction.machinename, count(dttransaction.machinename) dttransaction join dthotelreservation on dttransaction.transactionid = dthotelreservation.transactionid , dthotelreservation.hcomcid in (415428, 415429, 415430, 415431, 415432) group dttransaction.machinename
if want results date, include in group by
. instance,
select dttransaction.machinename, convert(varchar(10),bookedon,101), count(dttransaction.machinename) dttransaction join dthotelreservation on dttransaction.transactionid = dthotelreservation.transactionid , dthotelreservation.hcomcid in (415428, 415429, 415430, 415431, 415432) group dttransaction.machinename, convert(varchar(10),bookedon,101) order 1, max(bookedon)
i included order by
clause, results in order date within each machine name.
Comments
Post a Comment