SQL Query

[Soniya]

Hi All,

How can I Group data in an Sql query based on month while
the data on the table is based on Date?

For Eg.

Day Item Sales Purchase Amount


TIA
Soniya

 

[Nick Hodge]

Soniya

This sound like a perfect scenario for a pivot table. Take the option
Data>Pivot table report and navigate to the sql table or view. This will
bring the data to the pivot table designer.

Drag the Day and Item fields as row fields and then the others as values

Once you have them in as day data right click on the Day header and select
Group. Select Months and if there is more than one year's worth, select
years as well.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Some hints:

Can't tell what your data looks like or where its is held i.e. Excel,
Access, SQL Server , Oracle or ?.

For SQL Server, (should also work when using Jet 4.0 (i.e. text, Excel,
Access), a solution is:

select distinct(dob),
count(membno) as Freq,
month(dob) as mth ,
sum(spa) as totspa
from basic
group by month(dob),dob,spa

where
basic = the table
dob = a date (date of birth)
membno = numeric, distinct on each row (same for character data)
spa = numeric, an amount that can be summed.

You can try to adapt this to your data.