July 2, 2008 at 1:14 am
Hi
Please help
i have a table with the following columns
id identity
Event_Startdatedatetime
Venue_Countryvarchar(50)
isPosted bit
Now I need to show the report In the following manner
Total number of records,Country, Count of posted records ( isposted = 1) on daily basis
I wrote a query it is working fine but I am not able to get number posted records with that query
Please help me
The query is as follows
select Count(A.Event_Startdate) as TotalCount ,A.Venue_Country,convert(varchar(10),A.Event_Startdate,101) as StartDate
,Count(A.isposted) as Posted
from tblevents A where A.venue_country_Code='US'
and YEAR(A.Event_StartDate) ='2008'
and DATENAME(month,A.Event_StartDate) = 'JULY'
group by convert(varchar(10),A.Event_Startdate,101),A.Venue_Country
Result is as follows
Total Count CoutryStartDatePosted
34 United States07/01/200834
13 United States07/02/200813
Here the problem with the posted column. It is returning the total count which is not correct. Please correct the query
please help me
10x in adv.
July 2, 2008 at 1:31 am
If I understand you right you need to count only the items where the isposted bit is set. You can do this by:
SELECT COUNT(A.Event_Startdate) AS TotalCount
, A.Venue_Country
, CONVERT(VARCHAR(10), A.Event_Startdate, 101) AS StartDate
, SUM(CASE WHEN A.isposted = 1 THEN 1
ELSE 0
END) AS Posted
FROM tblevents A
WHERE A.venue_country_Code = 'US'
AND YEAR(A.Event_StartDate) = '2008'
AND DATENAME(month, A.Event_StartDate) = 'JULY'
GROUP BY CONVERT(VARCHAR(10), A.Event_Startdate, 101)
, A.Venue_Country
Regards,
Andras
July 2, 2008 at 1:40 am
Use the co-related sub query
or self join
July 2, 2008 at 3:36 am
10x a lot Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply