Do you know a Single Query To Get Item Count? 2

[sipos]

hello everyone,

i'm using FPD26

and i have a table like this
(table_01)
trans_id code
0001 A
0001 B
0001 A
0002 C
0002 A
0002 B

and i want to have a result like this
(table_02)
trans_id tot_item
0001 2
0002 3

usually i create two query, the first one use to group the code on transaction_id and save it to temporary table, and the resulting table is like this
(tmp_tbl)
trans_id code
0001 A
0001 B
0002 A
0002 B
0002 C
statement: select trans_id,code;
from table_01;
group by trans_id,code;
into cursor temp_tbl

and the second query will use to count this temp table for each transaction_id to get the result as table_02.
statement: select trans_id,count(trans_id);
from temp_tbl;
group by trans_id;
into tbl table_02

Now my question is ...
Do any of you know a query using only single SQL select statement which resulting a table equal to table_02 above?

 

[JRB-Bldr]

In your Table_01 you have
0001 - 3 records
0002 - 3 records

Either the results that you show in Table_02 above are wrong or you are using some other selection criteria which is not defined.

If you use

select Trans_ID, Count(code) as Tot_Item;
From table_01;
GROUP BY Trans_ID;
INTO TABLE Table_02

Your result will be
(table_02)
trans_id tot_item
0001 3
0002 3

Good Luck,



JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[cricket]

He must be counting distinct or UNIQUE code for each TRANS_ID

 

[JRB-Bldr]

To get what he indicated he wants

i want to have a result like this
(table_02)
trans_id          tot_item
0001              2
0002              3

He cannot want the UNIQUE or DISTINCT 'code' for trans_ID = 0002 or else his results would be different.

Good Luck,
JRB-Bldr

 

[TamarGranor]

SELECT trans_id, COUNT(Distinct Code) AS nCount ;
  FROM Table_01 ;
  GROUP BY trans_id ;
  INTO CURSOR Result

 

[sipos]

hello jrbbldr, cricket and tamargranor ... thanks for your responds.

my resultset is correct

001 = 2 (not 3) because in trans_id=001 there are 2 items (A and B). In quantity, yes A = 2 and B = 1, so the total is 3, but as items ... A=1 and B=1, so the total item is 2.

that's why i'm using 2 query. The first is used to distinct, and the second is use to count the distinct's result.

w/query1 w/query2
table_01 ----> tmp_table ----> table_02
001 A 001 A 2x 001 2x
001 B 001 B 1x
001 A
002 C 002 A 1x 002 3x
002 A 002 B 1x
002 B 002 C 1x

=================================

Yes using double query, we can get the result. but do you have any idea of a single query that produce the same result as table_02 (without intermediate tmp_table)?

 

[pmaeperez]

USE TABLE_01
select Trans_ID, code, COUNT(TRANS_ID+CODE) AS COMB ;
From TABLE_01;
GROUP BY TRANS_ID,CODE ;
INTO TABLE Table_02
select Trans_ID, code,COMB,COUNT(COMB) AS TOT_ITM ;
From TABLE_02;
GROUP BY TRANS_ID ;
INTO TABLE Table_03
USE
USE TABLE_03
BROWSE

**********FINAL REPORT***********
TRANS_ID CODE COMB TOT_ITM
0001 B 1 2
0002 C 1 3
*********************************

 

[TamarGranor]

Didn't the query I posted work for you?

Tamar

 

[sipos]

yes tamargranor, thanks for your query, but do you know a single query to create same exact result?

or is the double query is the best query we can get?

 

[Stella740pl]

sipos,

What double query are you talking about?

Didn't you notice that two people, jrbbldr and TamarGranor, already posted you single queries, just as you asked?

Have you tried them? They should do what you want. Take a look at all responses, not just the last one.

 

[sipos]

oopps ...

i'm sorry ...

i don't see the "distinct" word inside COUNT() statement
which is given by tamargranor

yes ... it is working like magic. The word "distinct" is the magic word for COUNT to distinct the code.

i never know that statement "DISTINCT" can be used inside COUNT statement ... oh how stupid i am

thank you so much especially for tamargranor and you too stella740pl for remind me

 

[sipos]

BTW, i just check in the help online for Foxpro 2.6 DOS

and i cannot find the explanation that word "distinct" can be used inside statement COUNT() in select query.

this is a very brand new to me ...