how to apply Group By to this SQL Query

Question

Hello, I'm trying to make a list of inventory to be counted, provided the item has had a sale in the last 2 months: I'm using Pervasive SQL and it's a BusinessVision table. This query works, but I don't know how to aggregate to have one item displayed:

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND",
"SALES_HISTORY_HEADER"."IN_DATE"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'

Ok. This query would give me repeated results of a part number, with different dates. I just want to have one occurrence for each part number, provided it as sold in the last 2 months. For example:

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090810 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090811 
A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000   20090812

I would prefer simply have it like this:

A6001-O15P   HP 700-101-O White 15" Perf   yds        0.00000      915.00000

and on the next row the next product. How can I do that?

EDITED PART AFTER SOME ANSWERS: Thanks for the suggestion about DISTINCT. I don't know why it repeats results yet. See (part of) the result of the query (not including "SALES_HISTORY_HEADER"."IN_DATE":

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

And when using the GROUP BY, or DISTINCT I get these results:

CODE           INV_DESCRIPTION                 BVSTKUOM   INV_COMMITTED   ONHAND    
--------------------------------------------------------- --------------- ----------
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         50.00000  
A6001-O15NP    HP 700-101-O White 15" NP       yds        0.00000         180.00000 
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         0.00000   
A6001-O15P     HP 700-101-O White 15" Perf     yds        0.00000         915.00000

I would expect just 2 rows, the ones with 180 and 915.

Answer 1

I don't know about Pervasive SQL, but normally you have to group by all the columns you want grouped, in this case that's all the columns (except "SALES_HISTORY_HEADER"."IN_DATE", which isn't in your desired output)

SELECT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'
group by "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"

Answer 2

Are you sure you're not looking for SELECT DISTINCT, which returns a single row if there are duplicates? If you exclude the IN_DATE from your SELECT, I think this is what you want.

SELECT DISTINCT "INVENTORY"."CODE", "INVENTORY"."INV_DESCRIPTION", 
"INVENTORY"."BVSTKUOM", "INVENTORY"."INV_COMMITTED", 
 "INVENTORY"."ONHAND"
FROM "INVENTORY" INNER JOIN "SALES_HISTORY_DETAIL" ON "SALES_HISTORY_DETAIL"."CODE" = "INVENTORY"."CODE" INNER JOIN "SALES_HISTORY_HEADER" ON "SALES_HISTORY_HEADER"."NUMBER" = "SALES_HISTORY_DETAIL"."NUMBER"
    where "INVENTORY"."PROD" like 'A6O%' AND "SALES_HISTORY_HEADER"."IN_DATE" > '20090731'

Answer 3

So the Pervasive Control Center (8.70.014.000) doesn't have an explain plan or anything that looks like profiling, so I can't speak beyond personal perception. But to me, the GROUP BY feels a tad faster:

    SELECT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number
  GROUP BY t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date

Mind that Pervasive requires you to include all columns that don't have aggregate functions performed.

Version using DISTINCT:

    SELECT DISTINCT t.code, 
           t.inv_description,
           t.bvstkuom,
           t.inv_committed,
           t.onhand,
           shh.in_date
      FROM "INVENTORY" t
INNER JOIN "SALES_HISTORY_DETAIL" shd ON shd.code = t.code
INNER JOIN "SALES_HISTORY_HEADER" shh ON shh.number = shd.number

What version of BV/Pervasive are you dealing with? I can test against v6/2000i if need be.

Answer 4

Well, it turns out that the problem with duplicates was because I forgot to filter out warehouses. I should have added in the where clause this:

WHERE "INVENTORY"."WHSE" = '00'

This took care of the problem