Help making an SQL query 1

[Luiz Eduh]

Hello,

I'm currently learning SQL and I need some help with the request below;

I currently have a Table called CUSTABLE1 with the following FIELDS: cus_name, cus_id, pur_date, sales_type, sales_total,

I need help making a query to export the fields above listing only when pur_date is between 1/1/16 – 1/31/16 where field sales_type = either "a" or "A"
Can the results of "a" and "A" of sales_total be sum and shown in 2 different columns?


Select custable1.cus_name, custable1.cus_id, custable1.pur_date, custable1.sales_type, custable1.sales_total;
from custable1 where custable1.pur_date > date(2016,01,01) and custable1.pur_date < date(2016,01,30);
into cursor temp1

The above is what I have so far, I can't figure out how to filter the custable1.sales_type and sum the sales_total either.
Any help will be much appreciated.

Thank you

 

[Olaf Doschke]

[tt]where ... and (sales_type = "a" or sales_type = "A")[/tt]

you might also use

[tt]where ... and (UPPER(sales_type) = "A")[/tt]

Just a sidenote on performance: The second condition is shorter, but both will be faster with an index and if you index sales_type the two conditions in the first clauses will be better optimized than the second clause UPPER(sales_type), even though it's two comparisons. If you index on UPPER(sales_type) that clause will be optimized, but not the first ones. What index is best for your usage depends much on how many of your queries should rather be case sensitive or insensitive.

You could SET COLLATE TO GENERAL and VFP will handle data case insensitive overall, but then can't distinguish between "a" and "A", so obviously you'll want to stay with the default [tt]MACHINE[/tt] collation, when you want to group by "a" and "A".

That leads to the grouping topic.
To not get the single rows, but sum them, you need a GROUP BY and then SUM(sales_total) as sales_total_total:

Select custable1.sales_type, SUM(custable1.sales_total) as sales_total_total;
from custable1 ;
group by sales_type;
where custable1.pur_date >= date(2016,01,01) and custable1.pur_date <= date(2016,01,30);
and (sales_type = "a" or sales_type = "A");
into cursor temp1

By the way, you might also use where custable1.pur_date between date(2016,01,01) and date(2016,01,30).

Bye, Olaf.

 

[Olaf Doschke]

One question: Is pur_date a date field or a datetime field? The comparison of a datetime and date is possible, but will have other results, eg any datetime on January 30 after midnight is after DATE(2016,1,30), while a date January 30 of cause equals DATE(2016,1,30). For a comparison of DATE(2016,1,30) with a datetime the DATE(2016,1,30) is expanded with a time portion of midnight, obviously that makes a difference. t's easier to NOT compute end of month, but take 1st of next month and exclude it from the span, that works with both datetime where it's the first moment of the next month and date, where it is the whole day of the next month, but it doesn't matter when it's excluded.

You might want to take the span of midnight, 1st of a month to midnight, 1st of next month excluding the upper datetime, then the best clauses for that are:
[tt]pur_date>=DATE(2016,1,1) AND pur_date<DATE(2016,2,1)[/tt]
and more generic, for a certain year yy and month mm:
[tt]pur_date>=DATE(yy,mm,1) AND pur_date<GOMONTH(DATE(yy,mm,1),1)[/tt]

This again profits from the automatic conversions of dates to datetimes, when necessary for the comparison. Both of these will work no matter if pur_date is type date or type datetime. For BETWEEN you'd need the last day or last second of a month, as the upper limit is included, but if your data is about the moment of purchase you will very seldom have midnight datetimes, unless it's about a 24h or online shop with high volume of purchases.

Bye, Olaf.

 

[Luiz Eduh]

Thank you so much, you guided me to the right direction and was able to obtain the data I needed.