Trying to do a conditional count...alongside a full results set

I was hoping someone may be able to help me with a SQL statement I'm
trying to come up with. I know I've done something successfully like
this in the past and that there's probably a simple way of doing it,
but I'm having a bit of a mental block at the moment.

What I'm trying to do is return, from a table APP_SESSION a list of
applications (field APPNAME) with the servers they are running on
(field SERVERNAME), grouped by server then app. However, the thing I've
hit a brick wall on is that I need a complete list of all apps
(regardless of when they've been accessed), and then for each app per
server, a count of the number of times it's been accessed within the
last month ONLY.

I tried the following, using two instances of the same table (one
'total', one within the last month or so) but it doesn't work as I'm
using an outer join so in fact returns all instances. I think what I
need to do instead is some kind of nested select, but am a bit stuck
for the time being. Any advice would be gratefully received!


SELECT TOP 100 PERCENT TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME,

COUNT(MONTHSESSIONS.SESSIONID) AS
& #91;NUMBERTHISMONTH]


FROM
APP_SESSION TOTALSESSIONS
LEFT OUTER JOIN
APP_SESSION MONTHSESSIONS ON
MONTHSESSIONS.SESSIONID = TOTALSESSIONS.SESSIONID
AND
MONTHSESSIONS.SESSIONSTART > CONVERT(DATETIME,
'2005-11-01 00:00:00', 102)

GROUP BY TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME
ORDER BY TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME


Many thanks.

Does this work correctly?

select a.app, a.server, c.NumberThisMonth
from
APP_SESSION a
left join
(
select a.app, a.server, Count(SESSIONID) as NumberThisMonth
from APP_SESSION
where SESSIONSTART > CONVERT(DATETIME, '2005-11-01 00:00:00', 102)
group by a.app, a.server
) c
ON a.app = c.app and a.server = c.server
group by a.app, a.server



MC


<mj.redfox.mj@gmail.com> wrote in message
news:1133257816.297068.38650@g14g2000cwa.googlegroups.com...
>I was hoping someone may be able to help me with a SQL statement I'm
> trying to come up with. I know I've done something successfully like
> this in the past and that there's probably a simple way of doing it,
> but I'm having a bit of a mental block at the moment.
>
> What I'm trying to do is return, from a table APP_SESSION a list of
> applications (field APPNAME) with the servers they are running on
> (field SERVERNAME), grouped by server then app. However, the thing I've
> hit a brick wall on is that I need a complete list of all apps
> (regardless of when they've been accessed), and then for each app per
> server, a count of the number of times it's been accessed within the
> last month ONLY.
>
> I tried the following, using two instances of the same table (one
> 'total', one within the last month or so) but it doesn't work as I'm
> using an outer join so in fact returns all instances. I think what I
> need to do instead is some kind of nested select, but am a bit stuck
> for the time being. Any advice would be gratefully received!
>
>
> SELECT TOP 100 PERCENT TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME,
>
>                      COUNT(MONTHSESSIONS.SESSIONID) AS
> & #91;NUMBERTHISMONTH]

>
> FROM
>                      APP_SESSION TOTALSESSIONS
> LEFT OUTER JOIN
>                      APP_SESSION MONTHSESSIONS ON
>                      MONTHSESSIONS.SESSIONID = TOTALSESSIONS.SESSIONID
> AND
>                      MONTHSESSIONS.SESSIONSTART > CONVERT(DATETIME,
> '2005-11-01 00:00:00', 102)
>
> GROUP BY TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME
> ORDER BY TOTALSESSIONS.SERVERNAME, TOTALSESSIONS.APPNAME
>
>
> Many thanks.
>

I do believe you've cracked it!

Thank you so much!



MC  wrote:[color=darkred
]
> Does this work correctly?
>
> select a.app, a.server, c.NumberThisMonth
> from
>     APP_SESSION a
>     left join
>     (
>     select a.app, a.server, Count(SESSIONID) as NumberThisMonth
>     from APP_SESSION
>     where SESSIONSTART > CONVERT(DATETIME, '2005-11-01 00:00:00', 102)
>     group by a.app, a.server
> ) c
>     ON a.app = c.app and a.server = c.server
> group by a.app, a.server
>
>
>
> MC
>
>
> <mj.redfox.mj@gmail.com> wrote in message
> news:1133257816.297068.38650@g14g2000cwa.googlegroups.com... 

Not a problem, glad I could help.
Just be carefull, it will return NULL if theres no sessions for the
app/server pair. Perhaps you should wrap isnull around. Then again, perhaps
not. It depends :).

MC


<mj.redfox.mj@gmail.com> wrote in message
news:1133272720.612635.309920@g44g2000cwa.googlegroups.com...
>I do believe you've cracked it!
>
> Thank you so much!
>
>
>
> MC wrote: 
>