SQL statement question 2

[chpicker]

I am still having fun trying to figure out how to properly construct an SQL statement to pull up the data I want. I've figured out a few of them, but there are still things I'm unsure of. Take this example:

2 tables, one contains Orders, the other contains Orderitems. Each item in Orderitems has a numeric Manifest which corresponds to the same field in Orders. Both tables are indexed by Manifest, but in Orders it is the Primary key.

I want to construct a SELECT statement that will give me one record per Manifest from Orders in a given date range with a COUNT of the number of matching records in Orderitems. I can't figure it out.

To go a step further, I want to construct another SELECT statement that gives me one record per Manifest from Orders in a given date range (as above), but the COUNT of matching Orderitems should ONLY reflect items that are on backorder (logical field in Orderitems).

Can anyone help me figure these out? Also, could anyone point me to a good online SQL reference?

 

[jimstarr]

Try this for step 1:

select orders.manifest, count(items.manifest) from orders, items where orders.manifest = items.manifest and between(orders.date, begdate, enddate) group by 1

Jim

 

[chpicker]

Aha! Thanks, Jim, that worked. I had tried exactly that, but didn't have the "group by" clause in there. That was giving me a single record with a (seemingly) random Manifest number and a total count of records in Items for the given date range. Just telling it to "Group by Manifest" causes it to split it properly.

Any suggestions for Step 2, then? Or where I might find a good online reference to SQL?

 

[jimstarr]

Step 2:

select orders.manifest, count(items.manifest) from orders, items where orders.manifest = items.manifest and between(orders.date, begdate, enddate) and item.backorder = .t. group by 1

Jim

 

[chpicker]

Yay! Ok, thanks again, Jim )

One more thing (I know, I keep on taking, hehe)

I tried to expand on step 2. What I need is multiple counts of different things within the same record. For instance, for each manifest I need a count of the number of items on backorder, the number of items that were sold at a discounted price (another logical field), and the number of items that were over $50. Some of these can overlap. I tried this:

select orders.manifest, count(items.manifest),count(items2.manifest) from orders, items,items as items2 where orders.manifest = items.manifest and between(orders.date, begdate, enddate) and item.backorder = .t. and items2.discount=.T. group by 1

What I end up with is less records than the above, BOTH count columns showing the exact same number, and those counts all being too high. I also tried changing "items as items2" to just "items2" and putting "use items in 0 again alias items2" before the select statement, but that came up with the same result.

What did I do wrong?

 

[chpicker]

Woops...I did forget another part that I had added. The statement I tried should look like this:

select orders.manifest, count(items.manifest),count(items2.manifest) from orders, items,items as items2 where orders.manifest = items.manifest and orders.manifest=items2.manifest and between(orders.date, begdate, enddate) and item.backorder = .t. and items2.discount=.T. group by 1

 

[rajeevnandanmishra]

Hi chpicker,
Try this,
select a.manifest, ;
sum(iif(b.backorder=.t.,1,0)) as backorder, ;
sum(iif(b.discount=.t.,1,0)) as discount, ;
sum(iif(b.rate>50,1,0)) as rate_50 ;
from orders a, items b ;
where a.manifest=b.manifest ;
group by 1

 

[chpicker]

Thanks rajeevnandanmishra...that worked perfectly!