Creating Cumulative column in Group by query (1 Viewer)

masoud_sedighy

Registered User.
Local time
Today, 13:25
Joined
Dec 10, 2011
Messages
132
I have a table and its name is “ztbl_Line_Trend” and its data is like below:
Date_List DISCIPLINE Done_Doc

9/21/2009 Building 2
9/22/2009 Building 1
9/23/2009 Building 3
9/21/2009 CIVIL 3
9/22/2009 CIVIL 1
9/23/2009 CIVIL 1
1/13/2010 Building 4
1/14/2010 Building 1
1/15/2010 Building 5
1/13/2010 CIVIL 4
1/14/2010 CIVIL 3
1/15/2010 CIVIL 2

Now I would like to make a query that summarize data by “year- month” nd “DISCIPLINE” and then calculate summary for [Done_Doc] field and also calculate cumulative [Done_Doc] for each “year-month” according to below:

Year_Month DISCIPLINE SumOfDone_Doc cummulative_Done_Doc

2009-09 Building 6 6
2009-09 CIVIL 5 5
2010-01 Building 10 16
2010-01 CIVIL 9 14

I have tried to do it, until below query, and it had not problem, but I have problem for creating cumulative column in query.

SELECT Format([date_list],"yyyy-mm") AS AllocateMonth, ztbl_Line_Trend.DISCIPLINE, Sum(ztbl_Line_Trend.Done_Doc) AS SumOfDone_Doc
FROM ztbl_Line_Trend
GROUP BY Format([date_list],"yyyy-mm"), ztbl_Line_Trend.DISCIPLINE


For creating “cummulative_Done_Doc” column, I tried to create it with a sub-query but I get “error”:

cummulative_Done_Doc: (SELECT SUM(T1.[Done_Doce]) from ztbl_Line_Trend AS T1 WHERE (YEAR([T1.Date_list])<= YEAR([ztbl_Line_Trend.Date_list])) AND (MONTH([T1.Date_list])<= MONTH([ztbl_Line_Trend.Date_list])))

Error: You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:25
Joined
May 7, 2009
Messages
19,169
SELECT DISTINCT Format(T1.Date_List,"yyyy-mm") AS Year_Month, T1.Discipline, (SELECT Sum(Done_Doc) FROM ztbl_Line_Trend AS T2 WHERE Format(T2.Date_List,"yyyy-mm") = Format(T1.Date_List,"yyyy-mm") AND T2.Discipline = T1.Discipline) AS Sum_DoneDocs, (SELECT Sum(Done_Doc) FROM ztbl_Line_Trend AS T2 WHERE Format(T2.Date_List,"yyyy-mm") <= Format(T1.Date_List,"yyyy-mm") AND T2.Discipline = T1.Discipline) AS Cumm_DoneDocs FROM ztbl_Line_Trend AS T1;
 

masoud_sedighy

Registered User.
Local time
Today, 13:25
Joined
Dec 10, 2011
Messages
132
I checked the query with more than 1000 records in the table was very slow. I put indexes on the table but still query is very slow. please help how i can solve the problem
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:25
Joined
May 7, 2009
Messages
19,169
try this if this is faster:

SELECT Format(t1.[Date_List],"yyyy-mm") AS DateList, t1.Discipline, Sum(t1.Done_Doc) AS SumOfDone_Doc, (SELECT SUM(Done_Doc) FROM ztbl_Line_Trend WHERE Discipline = T1.Discipline AND Format(Date_List,"yyyy-mm")<=Format(T1.Date_List,"yyyy-mm")) AS CummDoneDoc
FROM ztbl_Line_Trend AS t1
GROUP BY Format(t1.[Date_List],"yyyy-mm"), t1.Discipline;
 

masoud_sedighy

Registered User.
Local time
Today, 13:25
Joined
Dec 10, 2011
Messages
132
thanks, this query quickly comes up. but surveying in the query is slow.
 

Users who are viewing this thread

Top Bottom