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
 General SQL Server Forums
 New to SQL Server Programming
 NEW- need help!!!

Author  Topic 

desperate
Starting Member

21 Posts
Posted - 2008-10-16 : 08:34:42
Hey All,
New here
I'm working on a project in access right now. I was given 9 months (Jan-Sep) in 9 different queries. From those queries, I needed to produce a single query portraying the averages of my information. My SQL looks like the following (Diff Date = the average column in the other queries.. this is the only way I can bring everything together in one query without freezing my computer):

SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1]
FROM [MR to PO FEB Query];
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2]
FROM [MR to PO MAR Query];
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3]
FROM [MR to PO APR Query];
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4]
FROM [MR to PO MAY Query];
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5]
FROM [MR to PO JUN Query];
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6]
FROM [MR to PO JUL Query];
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7]
FROM [MR to PO AUG Query];
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8]
FROM [MR to PO SEP Query];

Here's a screen shot:



The fields are all in numerical order.. those aren't the averages in order for Jan-Sep.. can anyone shed some insight??

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-16 : 08:46:04
SELECT [AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2
FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4
FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5
FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7
FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8
FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9
FROM [MR to PO SEP Query]
) as T ORDER BY month_value


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-16 : 08:50:22
quote:
Originally posted by madhivanan

SELECT [AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2
FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4
FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5
FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7
FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8
FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9
FROM [MR to PO SEP Query]
) as T ORDER BY month_value


Madhivanan

Failing to plan is Planning to fail


I think I'm in love with you <3. Thank you so much for the help! Worked perfectly.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-10-16 : 08:57:31
quote:
Originally posted by desperate

quote:
Originally posted by madhivanan

SELECT [AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2
FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4
FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5
FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7
FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8
FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9
FROM [MR to PO SEP Query]
) as T ORDER BY month_value


Madhivanan

Failing to plan is Planning to fail


I think I'm in love with you <3. Thank you so much for the help! Worked perfectly.

You are welcome

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-16 : 12:46:49
quote:
Originally posted by madhivanan

quote:
Originally posted by desperate

quote:
Originally posted by madhivanan

SELECT [AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2
FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4
FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5
FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7
FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8
FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9
FROM [MR to PO SEP Query]
) as T ORDER BY month_value


Madhivanan

Failing to plan is Planning to fail


I think I'm in love with you <3. Thank you so much for the help! Worked perfectly.

You are welcome

Madhivanan

Failing to plan is Planning to fail


One more question for you... if I want to add another column so the screen shot will have two columns (one saying Month [with Jan-Sep]) and the other column being the "DiffDate" as shown above, how can I add that to the SQL?? I know how to do it via design view but my query will only go between SQL/Datasheet view.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-16 : 12:54:56
quote:
Originally posted by desperate

One more question for you... if I want to add another column so the screen shot will have two columns (one saying Month [with Jan-Sep]) and the other column being the "DiffDate" as shown above, how can I add that to the SQL?? I know how to do it via design view but my query will only go between SQL/Datasheet view.


do it like below

SELECT month,[AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS monthFROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) as T ORDER BY month_value
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-16 : 13:14:22
quote:
Originally posted by visakh16

quote:
Originally posted by desperate

One more question for you... if I want to add another column so the screen shot will have two columns (one saying Month [with Jan-Sep]) and the other column being the "DiffDate" as shown above, how can I add that to the SQL?? I know how to do it via design view but my query will only go between SQL/Datasheet view.


do it like below

SELECT month,[AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS monthFROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) as T ORDER BY month_value



When I pasted that in my SQL I got:
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-16 : 13:22:20
seem like its alias month which is reserved word. wrap in brackets and try

SELECT [month],[AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) as T ORDER BY month_value
Go to Top of Page

desperate
Starting Member

21 Posts
Posted - 2008-10-16 : 13:25:31
quote:
Originally posted by visakh16

seem like its alias month which is reserved word. wrap in brackets and try

SELECT [month],[AvgOfDIFF DATE] FROM
(
SELECT Avg([MR to PO JAN Query].[DIFF DATE]) AS [AvgOfDIFF DATE], 1 as month_value,'Jan' AS [month]
FROM [MR to PO JAN Query]
UNION SELECT Avg([MR to PO FEB Query].[DIFF DATE]) AS [AvgOfDIFF DATE1],2,'Feb' FROM [MR to PO FEB Query]
UNION SELECT Avg([MR to PO MAR Query].[DIFF DATE]) AS [AvgOfDIFF DATE2],3,'Mar'
FROM [MR to PO MAR Query]
UNION SELECT Avg([MR to PO APR Query].[DIFF DATE]) AS [AvgOfDIFF DATE3],4,'Apr'FROM [MR to PO APR Query]
UNION SELECT Avg([MR to PO MAY Query].[DIFF DATE]) AS [AvgOfDIFF DATE4],5,'May'FROM [MR to PO MAY Query]
UNION SELECT Avg([MR to PO JUN Query].[DIFF DATE]) AS [AvgOfDIFF DATE5],6,'Jun'
FROM [MR to PO JUN Query]
UNION SELECT Avg([MR to PO JUL Query].[DIFF DATE]) AS [AvgOfDIFF DATE6],7,'Jul'FROM [MR to PO JUL Query]
UNION SELECT Avg([MR to PO AUG Query].[DIFF DATE]) AS [AvgOfDIFF DATE7],8,'Aug'FROM [MR to PO AUG Query]
UNION SELECT Avg([MR to PO SEP Query].[DIFF DATE]) AS [AvgOfDIFF DATE8],9,'Sep'FROM [MR to PO SEP Query]
) as T ORDER BY month_value



PERFECT Thanks for your help!! You guys are real genius's here, haha.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-10-16 : 13:28:10
quote:
Originally posted by desperate

PERFECT Thanks for your help!! You guys are real genius's here, haha.


welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site