Subquery?

[r***@gmail.com]

Hello,

Here is my Projects table:

RowID User DateCreated Proj Hours
1 Dan 2013-06-21 100A 6
2 Dan 2013-06-22 100A 3
3 Dan 2013-06-25 300A 1
4 Dan 2013-06-26 300A 7
5 Joe 2013-06-22 500A 5
6 Joe 2013-06-23 500A 5
7 Joe 2013-06-27 600A 10

This is what I want the query to produce:

User Month Proj Hours %
Dan Jun 2012 100A 9 53
Dan Jun 2012 300A 8 47
Joe Jun 2012 500A 10 50
Joe Jun 2012 600A 10 50

The query should sum all of the individual user hours by individual by month
then calculate the % each project has of the total hours of that individual user.

Below is the query I have so far but it's not getting % of project by user,
it's getting % of project from all projects. I think I need a subquery but
I don't know how to do that. Can someone help? I'm using SQL 2008 R2.

Thanks.


SELECT [User], right(convert(varchar, DateCreated, 106), 8) as 'Month', [Proj], SUM(Hours) AS [Hours],
100.0 * SUM(Hours) / SUM(SUM(Hours)) OVER () AS [%]
FROM Projects
GROUP BY User, right(convert(varchar, DateCreated, 106), 8), ProjNo
GO

[Erland Sommarskog]

Post by r***@gmail.com
Below is the query I have so far but it's not getting % of project by
user, it's getting % of project from all projects. I think I need a
subquery but I don't know how to do that. Can someone help? I'm using
SQL 2008 R2.

No, you don't need a subquery. Your query is given the percentage of
the hours across all users. Apparently, you want the percentage for
that user - and maybe month, that is not clear.

To this end you need to add a PARTITION BY to your OVER clause:

OVER(PARTITION BY [User]

[r***@gmail.com]

Post by Erland Sommarskog

Post by r***@gmail.com
Below is the query I have so far but it's not getting % of project by
user, it's getting % of project from all projects. I think I need a
subquery but I don't know how to do that. Can someone help? I'm using
SQL 2008 R2.

No, you don't need a subquery. Your query is given the percentage of
the hours across all users. Apparently, you want the percentage for
that user - and maybe month, that is not clear.
OVER(PARTITION BY [User]
--

Thank you very much. You were right, I only needed to add
(Partition By User, right(convert(varchar, DateCreated, 106), 8))
to OVER ().