Mike has picked up and expanded on one of the core problems I already mentioned, too: We don't have your data. It's impossible obviously, to give us all your data, but you can come up with just a few lines of code giving us enough sample data to understand your core problem. You can even translate your specific problem to some more common and well known schema, though invoicing is already a topic, which is very common and unexotic.
One of the things biting you yourself in making you understandable obviously is a lack of english knowledge, but even more biting you is your field names are not verbose and self explanatory. At least you used short names coming from english, eg sino= slaes invoice number, but that's not obvious. You are victim of the legacy foxpro and alos OS (DOS) limitations about name lengths, file names, table name,s field names. But that limit has lifted many years ago and you still did stay with short names.
We can only tach you generic things, to apply to your situation, as you don't let us get a final grip on your situation in enough depth to help you. And even that only sinks into your awareness of your problem with getting help from us, you don't even see why your information is insufficient, as what is blatantly clear to you is still not making any sense to us.
To get a self contained problem example, let me make an example of a count problem with very little code:
Code:
* Sample data for my problem
CREATE CURSOR crsInvoiceDetails (InvoiceDetailID integer, InvoiceID integer, Amount currency)
Insert Into crsInvoiceDetails Values (1, 1, $100)
Insert Into crsInvoiceDetails Values (2, 1, $200)
Insert Into crsInvoiceDetails Values (3, 2, $100)
Now how do I get the count of involved Invoices?
---
How this example has more clarity than yours is, the field name InvoiceID clearly says it's a unique value per invoice. The sample data given tells this is not the primray key of this data, as InvoiceID is 1 for two records, so it would be a foreign key. InvoiceDetailID could be a primary key, as it's unique, also its position as first field of a table it makes even more sense to see it as primary key. I could even be more clear in the problem description, but that can also be part of discussing the problem and answering questions of the experts. You should take questions serious, as trying to help you, not telling you what you should do, simply asking for more clarification of your problem to be able to help.
In this case I could not only tell you how to count the invoices, also how to create the grand total, that's simply
Code:
SELECT InvoiceID, SUM(Amount) as TotalAmount, Group By InvoiceID Into Cursor crsInvoiceTotals
lnCountOfInvoices = _TALLY && also RECCOUNT("crsInvoiceTotals")
I may want to count within the query result, that's not what SQL can give you at the stage of querying. It's a total count anyway, which makes no sense to be put into every record of the result. What I can take from the Invoicedetail data is the count of invoicedetails:
Code:
SELECT InvoiceID, SUM(Amount) as TotalAmount, Count(*) as DetailsCount Group By InvoiceID Into Cursor crsInvoiceTotals
lnCountOfInvoices = _TALLY && also RECCOUNT("crsInvoiceTotals")
The count of invoices still is _TALLY, not DetailsCount. It's just a coincidence the first invoice has 2 detail records, matching the total count of 2 invoices. The total count of invoicedetails is 3 and that's the SUM of the two Counts, overall, but that again would be a next step, another query on this query result.
It only makes sense to put fields into a result, that are attributes of a single record, attributes of the whole result are a separate single value and the count of records is easy, _TALLY exists for very long time.
And now a very interesting part is, if I would take Count(InvoiceID), that would still get the same count as COUNT(*), it doesn't count the InvoiceIDs, it counts all rows per grou, which have a non NULL InvoiceID, and that's still 2 for the first record and 1 for the second, the count of invoicedetails. It makes no difference in this case, as no InvoiceID is null. You could get the impression COUNT(InvoiceID) counts the distinct, different, unique invoiceIDs, but it doesn't, if that would be the case, the result would be 1 for each row, as each row is about one InvoiceID, I group by InvoiceID. If I wanted that, I would need COUNT(distinc InvoiceID), which indeed works and indeed gives 1 for each reuslt row. I'm still thinkin you want the sum of all these counts, and that's simple to calculate, as each count(distinct InvoiceID) GROUP By InvoiceID just is the product of 1 and the number of records, 1*_TALLY or 1*RECCOUNT("crsInvoiceTotals") or simply _TALLY or RECCOUNT("crsInvoiceTotals").
It seems you want something, that's not applicable to SQL or at least not applicable to be included in this level of result, it would simply need a SELECT Count(*) FROM crsInvoiceTotals, which can be shortened, as it's the count of records of crsInvoiceTotals, and we don't need another query to know that. Your idea of getting a result included is not working, you can't have a row per group and the count of groups in the same result. The count of groups is a secondary result, only the count of subgroups (eg the number of records aggregated in each group, which might differ) can be included in the result.
I I would want the 2 invoices within the result, that demand makes no sense in itself. It's like seeing the total count of invoices as attribute of each single invoice, but it's not an attribute of an invoice, it's an attribute of the list of all invoices. Even If you would simply left join a subquery doing that total count, I woould have 2 in all 2 records, the two already is there, though. If I would left join a query counting all invoice details, it would put 3 into all two result rows, that's still not an attribute of each invoice, it's an attribute of the list of invoicedetails. And - surprise - that's simply the count of the records if the initial sample data, RECCOUNT("crsInvoiceDetails").
The only count making sense in the crscrsInvoiceTotals is the COUNT(*) of invoicedetails, so you can see the total amount is summed from how many detail amounts. And then tblkene has hit that in his first answer. Have you tried it at all, have you seen how Count(*) and Count(InvoiceID) is the same result in my sample data? If not, then simply try out yourself in this code wrapping up all tests I talked about:
Code:
* sample data for my problem
CREATE CURSOR crsInvoiceDetails (InvoiceDetailID integer, InvoiceID integer, Amount currency)
Insert Into crsInvoiceDetails Values (1, 1, $100)
Insert Into crsInvoiceDetails Values (2, 1, $200)
Insert Into crsInvoiceDetails Values (3, 2, $100)
SELECT InvoiceID, ;
COUNT(distinct InvoiceID) as distinctcount, ;
COUNT(*) as groupcount, ;
COUNT(InvoiceID) as invoicecount, ;
SUM(Amount) ;
FROM crsInvoiceDetails GROUP BY InvoiceID
groupcount and invoicecount are the same, and that's not just by chance, it's because they only would differ, if some invoiceIDs are NULL, which wouldn't make sense, as you then had an invoice detail not belonging to any invoice, it would be an orphaned invoicedetail.
The dangerous implication you can make is COUNT(InvoiceID) gives the count of distinct invoice ids of the whole data. It's just coincidence in th first record, but you see the second record has 1. This query result by definition has one row PER InvoiceID because of GROUP BY InvoiceID, it's easy to remember that as it defines what grouping means in the sql sense, you get ONE record per group, you aggregate data, you not only partition data into the groups, you really reduce it to one row per group, and since this results from processing each group a final total total only could be after all result records. But query results are not like excel tables, they don't have one summaration row, to get a final total of totals, you need another query, at least if you need something more complex than just the row count, the row count is an attribute of the alias, RECCOUNT, so that doesn't need a second (or third) query. If you wanted the average number of invoicedetailIDs, though, that's not what you can get in a single query. AVG(COUNT(*)) isn't working, you can only get AVG(groupcount) in a next query.
Besides all this, if you want to play with your data and test your possibilities and their result, you can make a breakpoint and apply as many queries as you want on your intermediate result cursor sijunk, unless you query into cursor sijunk you never throw away or midify sijunk data. To be able to make your experiments over a longer period, several days for example, you could select the sijunk workarea and COPY TO C:\playdata\sijunk20160911.dbf, for example, this is saving your cursor as DBF. You can use this as persisted data and amke all kinds of experiments on this data as long as you need. Besides you can of course recreate a cursor by running the same code.
Bye, Olaf.
