Need Help in Grouping, Date & Percentage

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Need Help in Grouping, Date & Percentage

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2002-07-24 : 10:26:36

Hi I am having trouble creating a SQL to return me the Won % in each of the month. Can you please Help ?

ID, Status, Date
1, Won, 1 Jan 2002
2, Won, 1 Jan 2002
3, Lost, 5 Mar 2002
4, Lost, 7 Apr 2002
5, Lost, 4 Jan 2002
6, Won, 8 Mar 2002

I want the end result to be:
Won, Month
66.66%, Jan
50%, Mar
0%, Apr

Thanks
Matthew

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-07-24 : 14:29:43

select 100.0 * sum(iif(status='Won',1.0,0.0))/sum(1.0) , month([date]),year([date]) from t
group by month([date]),year([date])

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-09-10 : 15:23:24

Boy, I love SQLTeam! I was working on a GROUP BY with Percentage calculation, and I was SOOOoooooo close, but just couldn't get it to pop. Then I thinks to myself... "You know, I'm sure I've seen this discussed in the SQLTeam forums. I think I'll go do a search." And sure enough, LarsG had a solution here for Access, and I already had the right CASE structure for SQL, so it was an easy mod.

So just thought I'd post a Thanks to ALL!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources