How to make group on year

[alisaif]

Hi,

I have a table which contains records of at least 4 years. I want to sum up the Quantity Sold for the month of July only from 2009 to 2013 based on year.

The fields of the table are as follows:

1. sino c(8)
2. invdate d
3. ItemCode c(10)
4. quantity n(10,3)
5. rate n(10,2)

The field (invdate) contains the records from 2009 to 2013 and I want the total like;

2009 = 12,000
2010 = 13,500
2011 = 14,000
2012 = 13,000
2013 = 14,500

How can I do so by using Select-Sql .. group by

Thanks

Saif

 

[Olaf Doschke]

Select YEAR(invdate) as Year, SUM(quantity) as JulyQty FROM yourtable ;
Group by YEAR(invdate), MONTH(invdate) ;
WHERE Year(invdate) between 2009 and 2013 ;
and Month(invdate)=7

For all months:

Select YEAR(invdate) as Year, MONTH(invdate) as Month, SUM(quantity) as MonthQty FROM yourtable ;
Group by YEAR(invdate), MONTH(invdate) ;
WHERE Year(invdate) between 2009 and 2013

Bye, Olaf.

 

[Olaf Doschke]

You might want Sum(quantity*rate) instead, you know best.

Bye, Olaf.

 

[alisaif]

Thanks for the reply

Select YEAR(invdate) as Year, SUM(QntyMaj*RateMaj) as Major , SUM(QntyMin*RateMin) as JulyQty FROM tstsi ;
Group by YEAR(invdate), MONTH(invdate) WHERE Year(invdate) between 2009 and 2013 and Month(invdate)=7

giving an error "SQL: Column " is not found"

 

[JRB-Bldr]

"SQL: Column " is not found"

Obviously the error message is not totally accurate about the cause of the problem.

Year(invdate) between 2009 and 2013

What is your data source tstsi?
* Is it a VFP Data Table?
* Or is it a table in a SQL Server?

The syntax of the BETWEEN clause is different in each.
VFP: BETWEEN(Year(invdate),2009,2013)
SQL Server: as in your code

Additionally, VFP SQL Query syntax is more 'sensitive' to clause location than other SQL Server syntax.
You have the GROUP BY clause before the WHERE clause. In VFP the GROUP BY clause must follow any WHERE clauses.

Remember that the SQL Server syntax needs to be specific to the data source.
VFP and SQL Server SQL Query syntax are different in some ways.

Suggestion - If your data source is a table in a SQL Server and you get errors, run the code by itself in the SQL Server Management Studio and confirm correct syntax before using it in your VFP code.

Good Luck,
JRB-Bldr

 

[alisaif]

I am using cursor as follows:

SELECT a.sino,a.invdate,a.pcode,b.icode,b.qntymaj,b.qntymin,b.ratemaj,b.ratemin FROM simain a,sisub b WHERE a.sino = b.sino ;
AND icode = 'B1236' AND MONTH(invdate)=7 INTO CURSOR tstsi

Saif

 

[JRB-Bldr]

OK, but your first SQL Query code example shows: SELECT .... FROM tstsi
And you indicated that you were encountering an error with this Query.
My comments above were about the error message encountered in your first SQL Query code example.

And your second SQL Query code example shows: SELECT .... INTO CURSOR tstsi
But you say nothing as to whether or not this second one works OK or not.

Pardon my confusion but what is the question?

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Well, between syntax also works in VFP Sql, you state field types which are DBF but in your own query your field names don't match to the names initially given. So think about giving us correct information or adapt as needed.

The query itself works, if all field names exist.

Bye, Olaf.

 

[Dan Freeman]

The first query is trying to GROUP BY a value that isn't in the output list. (And there's a VFP keyword as a column name, which always makes my skin crawl a little bit.) I'm confused what this has to do with the second entirely different query too.

 

[Olaf Doschke]

Hm, seems that group by is not VFP compatible and you can only group by fields or a number indicating a column, which needs to be part of the result.

This works with sample data:

Create Cursor yourtable (invdate D, quantity N(10,3))
Insert into yourtable values (Date(2009,7,1),1)
Insert into yourtable values (Date(2009,7,2),2)
Insert into yourtable values (Date(2010,7,1),3)
Insert into yourtable values (Date(2010,7,2),4)
Insert into yourtable values (Date(2010,8,1),5)
Insert into yourtable values (Date(2010,8,2),6)
** expecting 2009, 3 (1+2) and 2010, 7 (3+4), no value for 2010, as data only is for august.

Select YEAR(invdate) as Year, MONTH(invdate) as Month,;
SUM(quantity) as JulyQty FROM yourtable;
Group by 1, 2;
WHERE Year(invdate) between 2009 and 2013 ;
and Month(invdate)=7

Bye, Olaf.

 

[Olaf Doschke]

Today is not my day. The description of course is wrong, you get a value for 2010, but not including the last two records.

You may skip the month column, and still can filter by WHERE:

Select YEAR(invdate) as Year,;
SUM(quantity) as JulyQty FROM yourtable;
Group by 1;
WHERE Year(invdate) between 2009 and 2013 ;
and Month(invdate)=7

Bye, Olaf.

 

[Olaf Doschke]

Hi Dan,

let's take aside the column name 'year' for the moment. Could name it nYear and it would be fine again.

It seems awkward for me to query a result only aggregating july data of several years, so I added a further query to aggregate all months. I can understand comparing this years july balance with previous years july balances makes sense, if your business is depending on seasons and has a "climate" so comparing july with june or february data is of no interest, but you want to compare equal months.

Once the machnism works and is clear, you can easily generalize, eg always querying the previous month data of previous 4 years would be:

Local ldRefDate, lnMaxYear, lnPreviousMonth
ldRefDate = Gomonth(Date(),-1) && some date in the previous month
lnPreviousMonth = Month(ldRefdate)
lnMaxYear = Year(ldRefdate) && eg in January 2014 would be 2013, as previous month is December 2013.

Select YEAR(invdate) as nYear,;
SUM(quantity) as PreviousMonthQty FROM yourtable;
Group by 1;
WHERE Year(invdate) between lnMaxYear-4 and lnMaxYear ;
and Month(invdate) = lnPreviousMonth

Bye, Olaf.

 

[alisaif]

Thanks and sorry for the late reply.

I got it by using the following syntax:

      sele year(invdate) as theYear, ;
         month(invdate) as theMonth, ;
      icode as icode, ;
      pcode as pcode, ;
      SUM(qntymaj) as totctn, ;
      SUM(qntymin) as totunit, ;
      SUM(qntymaj*ratemaj) as CtnAmt, ;
      SUM(qntymin*ratemin) as UnitAmt ;
      from tstsi where BETWEEN(Year(invdate),2009,2013) ;
      group by theYear, theMonth, icode, pcode ;
      into curs tempsi

Saif

 

[alisaif]

I am using the following in order to get the required result.

m_icode = 'B1236'
m_mon = 7
mYur1 = 2009
mYur2 = 2013

Select a.sino,;
a.invdate,;
a.pcode,;
b.icode,;
b.qntymaj,;
b.qntymin,;
b.ratemaj,;
b.ratemin From simain a,sisub b Where a.sino = b.sino And icode = m_icode And ;
MONTH(invdate)=m_mon Into Cursor tstsi

Sele Year(invdate) As theyear, ;
month(invdate) As themonth, ;
icode As icode, ;
pcode As pcode, ;
SUM(qntymaj) As totctn, ;
SUM(qntymin) As totunit, ;
SUM(qntymaj*ratemaj) As ctnamt, ;
SUM(qntymin*ratemin) As unitamt ;
from tstsi Where Between(Year(invdate),Myur1,Myur2) ;
group By theyear, themonth, icode, pcode ;
into Curs tempsi

Thanks

Saif

 

[alisaif]

I am using the following in order to get the required result.

m_icode = 'B1236'
m_mon   = 7
mYur1   = 2009
mYur2   = 2013

Select a.sino,;
   a.invdate,;
   a.pcode,;
   b.icode,;
   b.qntymaj,;
   b.qntymin,;
   b.ratemaj,;
   b.ratemin From simain a,sisub b Where a.sino = b.sino And icode = m_icode And ;
   MONTH(invdate)=m_mon Into Cursor tstsi

Sele Year(invdate) As theyear, ;
   month(invdate) As themonth, ;
   icode As icode, ;
   pcode As pcode, ;
   SUM(qntymaj) As totctn, ;
   SUM(qntymin) As totunit, ;
   SUM(qntymaj*ratemaj) As ctnamt, ;
   SUM(qntymin*ratemin) As unitamt ;
   from tstsi Where Between(Year(invdate),Myur1,Myur2) ;
   group By theyear, themonth, icode, pcode ;
   into Curs tempsi

Thanks

Saif

 

[Olaf Doschke]

As you do two queries, you could limit the first result to the years wanted already. I'd combine that into a single query, as only the initial query can make use of table indexes and rushmore optimistaion. The second query can be fast enough, as it only works on a subset of data, but the overall query speed can be fastest working on the full data, too.

Take that aside, you at least should also make the year filtering in the first query:

Select a.sino,;
   a.invdate,;
   a.pcode,;
   b.icode,;
   b.qntymaj,;
   b.qntymin,;
   b.ratemaj,;
   b.ratemin From simain a,sisub b Where a.sino = b.sino And icode = m_icode And ;
   MONTH(invdate)=m_mon ;
   AND Between(Year(invdate),Myur1,Myur2) ;
   Into Cursor tstsi

Then you don't need any where clause in the second query.

Bye, Olaf.

 

[alisaif]

Yes, I was looking for the same but how can I make group?

Thanks a lot.

Saif

 

[Olaf Doschke]

>how can I make group?
In exactly the same way.
What have you tried and what doesn't work?

Bye, Olaf.

 

[alisaif]

Thanks !

Saif