SQL Group and Sum Question

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

How might I go about running SQL query by summing a field grouped by Month?
something Like....???

SELECT [Date], Sum([Field2]) FROM Table1
GROUP BY [Date(Month)]

pls help

thanks
 
Try this --
SELECT Format([Date], "mmmm yyyy") AS [Month], Sum([Field2])
FROM Table1
GROUP BY Format([Date], "yyyymm");
 
I Get a Message....You tried to execute a query that does not include the
specified expression 'Format([Date],"mmmmyyyy")' as part of an aggregate
function.
Here is the complete SQL...

SELECT
Format([Date], "mmmm yyyy") AS [Month],
SUM([05PpCert]), SUM([05PpTaxPaid]),
SUM([06PpCert]), SUM([06PpTaxPaid]),
SUM([07PpCert]), SUM([07PpTaxPaid]),
SUM([08PpCert]), SUM([08PpTaxPaid]),
SUM([03RpCert]), SUM([03RpTaxPaid]),
SUM([04RpCert]), SUM([04RpTaxPaid]),
SUM([05RpCert]), SUM([05RpTaxPaid]),
SUM([06RpCert]), SUM([06RpTaxPaid]),
SUM([07RpCert]), SUM([07RpTaxPaid]),
SUM([08RpCert]), SUM([08RpTaxPaid])
FROM Collections
GROUP BY Format([Date], "yyyymm");


KARL DEWEY said:
Try this --
SELECT Format([Date], "mmmm yyyy") AS [Month], Sum([Field2])
FROM Table1
GROUP BY Format([Date], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


Gator said:
How might I go about running SQL query by summing a field grouped by Month?
something Like....???

SELECT [Date], Sum([Field2]) FROM Table1
GROUP BY [Date(Month)]

pls help

thanks
Click to expand...
Click to expand...
 
Did not test before, try this --
SELECT
Format([Date], "mmmm yyyy") AS [Month],
SUM([05PpCert]), SUM([05PpTaxPaid]),
SUM([06PpCert]), SUM([06PpTaxPaid]),
SUM([07PpCert]), SUM([07PpTaxPaid]),
SUM([08PpCert]), SUM([08PpTaxPaid]),
SUM([03RpCert]), SUM([03RpTaxPaid]),
SUM([04RpCert]), SUM([04RpTaxPaid]),
SUM([05RpCert]), SUM([05RpTaxPaid]),
SUM([06RpCert]), SUM([06RpTaxPaid]),
SUM([07RpCert]), SUM([07RpTaxPaid]),
SUM([08RpCert]), SUM([08RpTaxPaid])
FROM Collections
GROUP BY Format([Date], "mmmm yyyy"), Format([Date], "yyyymm")
ORDER BY Format([Date], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


Gator said:
I Get a Message....You tried to execute a query that does not include the
specified expression 'Format([Date],"mmmmyyyy")' as part of an aggregate
function.
Here is the complete SQL...

SELECT
Format([Date], "mmmm yyyy") AS [Month],
SUM([05PpCert]), SUM([05PpTaxPaid]),
SUM([06PpCert]), SUM([06PpTaxPaid]),
SUM([07PpCert]), SUM([07PpTaxPaid]),
SUM([08PpCert]), SUM([08PpTaxPaid]),
SUM([03RpCert]), SUM([03RpTaxPaid]),
SUM([04RpCert]), SUM([04RpTaxPaid]),
SUM([05RpCert]), SUM([05RpTaxPaid]),
SUM([06RpCert]), SUM([06RpTaxPaid]),
SUM([07RpCert]), SUM([07RpTaxPaid]),
SUM([08RpCert]), SUM([08RpTaxPaid])
FROM Collections
GROUP BY Format([Date], "yyyymm");


KARL DEWEY said:
Try this --
SELECT Format([Date], "mmmm yyyy") AS [Month], Sum([Field2])
FROM Table1
GROUP BY Format([Date], "yyyymm");

--
KARL DEWEY
Build a little - Test a little


Gator said:
How might I go about running SQL query by summing a field grouped by Month?
something Like....???

SELECT [Date], Sum([Field2]) FROM Table1
GROUP BY [Date(Month)]

pls help

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top