Count of same column appearing twice in same query

andy151982 · May 14th, 2007, 10:10 AM

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreate d))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS MagazineAdCount
FROM orderformlineitems
where product_variant_id = '4010436709979469536'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek MagazineAdCount
04/16/2007 04/23/2007 8
04/23/2007 04/30/2007 15
04/30/2007 05/07/2007 5

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreate d))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS BannerAdCount
FROM orderformlineitems
where product_variant_id = '7453910328410493551'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek BannerAdCount
04/16/2007 04/23/2007 15
04/23/2007 04/30/2007 21
04/30/2007 05/07/2007 22

I WANT THE BELOW OUTPUT THRU A SINGLE QUERY.....

StartWeek EndWeek MagazineAdCount BannerAdCount

04/16/2007 04/23/2007 8 15
04/23/2007 04/30/2007 15 21
04/30/2007 05/07/2007 5 22

Can someone help ???

Thanks,

Andy

Peso · May 14th, 2007, 03:17 PM

Code:

SELECT        StartWeek,
        EndWeek,
        SUM(MagazineAdCount),
        SUM(BannerAdCount)
FROM        (
            SELECT    dateadd(day, datediff(day, 0, d_datecreated) / 7 * 7, 0) as StartWeek,
                dateadd(day, datediff(day, 0, d_datecreated) / 7 * 7, 6) as EndWeek,
                1 AS MagazineAdCount,
                0 AS BannerAdCount
            FROM    orderformlineitems
            where    product_variant_id = '4010436709979469536'

            UNION ALL

            SELECT    dateadd(day, datediff(day, 0, d_datecreated) / 7 * 7, 0),
                dateadd(day, datediff(day, 0, d_datecreated) / 7 * 7, 6),
                0,
                1
            FROM    orderformlineitems
            where    product_variant_id = '7453910328410493551'
        ) AS d
GROUP BY    StartWeek,
        EndWeek
ORDER BY    StartWeek