how to insert

[alisaif]

Hi,

How to insert "count" in the following:

         Sele icode As icode, ;
            sum(qntymaj) As qntymaj, ;
            sum(qntymin) As qntymin, ;
            sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As amount ;
            from sijunk1 ;
            group By icode into Curs tempsi1 Readwrite

I want to count the number of invoice generated.

Thanks

Saif

 

[Tore Bleken]

Is count(*) what you are looking for?

 

[Mike Lewis]

Once again, we are reduced to a guessing game to try figure out what a questioner is asking. In this case, it would be much easier if you could define "the number of invoice generated".

I'll assume that it's the number of records in the output cursor (Tempsi1). If so, then all you have to do is to look at [tt]_TALLY[/tt] - or, alternatively, [tt]RECCOUNT("Tempsi1")[/tt].

If that's not what you meant ... I'll leave it to wiser heads than mine to come up with a guess.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed nobody can know how an invoice is stored in your system and what an invoices would mean. Is an icode an invoice code? Is it unique per invoice? And are all icodes in the sijunk1 table/alias?

Then it's as simple as Count(*), as you do group by icode, the number of groups is the number of icodes and that (likely) is the number of invoices.

What the difficulty is for us is not knowing concrete data and its meaning, so we only are able to make educated guesses.

Another guess is you have a dedicated invoice table and could take its reccount(), but that also includes deleted rows and perhaps you only want to count open/yet unpaid invoices or any other condition on them, so it could also in the end turn out you better make that a separate query, unless you really are looking out for the count of records of your result.

Bye, Olaf.

 

[JRB-Bldr]

Like the others above I have to GUESS at what you are trying to get a Count of.

But going by what appears in your SQL Query (specifically: group By icode), my guess is that you want to get a Count of distinct ICodes that result from running your Query.

Since that Grouping will only generate a single query result record for each ICode, you must, again a GUESS, want to find out how many records the query generated.

There are many ways to get the Count using a secondary operation.

One of the easiest might be:

* --- Run your query code above ---

* --- [quote=]"I want to count the number of invoice generated."[/quote] ---
* --- Now determine the number of ICode records generated ---
SELECT tempsi1
nNoICodeRecords = RECCOUNT()

However you never tell us what you want to do with this count.

Good Luck,
JRB-Bldr

 

[alisaif]

Thanks for the reply!

I was looking as mentioned below:

   Sele pcode As pcode, ;
      location, ;
      full_name,;
      sum(qntymaj) As qntymaj, ;
      sum(qntymin) As qntymin, ;
      sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As amount, ;
      [highlight #FCE94F]COUNT(sino) As totsi[/highlight] From sijunk1 ;

Saif

 

[Olaf Doschke]

No future reader can make something out of this. What is sino? Is it set for every record or can it also be NULL? If it's never NULL this will be the same as COUNT(*). And as you don't group by anything with this query, that would be like RECCOUN('sijunk1') or _TALLY after the query.

Bye, Olaf.

 

[alisaif]

Hi,

Sino is Sales Invoice No. I just wanted to count how many invoices have been created during the period.

Query is here:

   Sele pcode As pcode, ;
      Location, ;
      full_name,;
      sum(qntymaj) As qntymaj, ;
      sum(qntymin) As qntymin, ;
      sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As amount, ;
      COUNT(sino) As totsi  ;
      From sijunk1 ;
      group By pcode, location, full_name Into Curs tempsi1 Readwrite

Thanks

Saif

 

[Mike Lewis]

Well, I expect other people here understand exactly what you want to know, but I still don't get it.

You started by asking about "number of invoices generated", without any clue as to what that meant. Then there was the confusion over whether you were asking for a total number of invoices, or the number in some grouping or other. Now you have introduced a new variable: "created during the period"?

So, what is a "period" in this context? The term suggests a period of time, but I can't see any time-related data in your code. If you were grouping on a range of dates, that would explain it, but clearly that is not the case.

If I am missing something obvious, my apologies. No doubt someone else will give you an answer. But it does look to me as if the guessing game is continuing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I think problem solved. But nowhere in this whole thread anyone was capable to tell you what to query. We don't have hands on your data and meaning of it, nor can we verify what you now show is okay or just works in this case. So, finally, if that solves it for you, fine. But it won't help anyone else and I don't see a way to tell confirm this is what you need. You seem to work with lots of DBFs you generate on the fly, eg tempsi1 may be just a subset of data from a certain period. But nobody can see how you arrived there. Nobody can give you the confirmation and approval you might want now, your contribution also is lost, as nobody knows your core tables and data.

Please have a read on this:

http://www.sscce.org/

Bye, Olaf.

 

[alisaif]

Hi,

You seem to work with lots of DBFs you generate on the fly, eg tempsi1 may be just a subset of data from a certain period. But nobody can see how you arrived there.

Yes, you are right, actually I have to give you the whole SQL which I am using on the fly.

The query was like that;

Select Simain.sino, Simain.invdate, Simain.pcode, Simain.zone,;
      Simain.location, Simain.salesman, Simain.merchand, Sisub.icode, Sisub.qntymaj, ;
      Sisub.qntymin, Sisub.ratemaj, Sisub.ratemin, icode.barcode, icode.full_desc, ;
      Customer.full_name, Kategory.maincat,;
      Kategory.category, Kategory.subcat1, Kategory.subcat2 ;
      FROM ;
      village!Simain ;
      INNER Join village!Sisub ;
      ON  Simain.sino = Sisub.sino ;
      INNER Join village!icode ;
      ON  Sisub.icode = icode.icode ;
      INNER Join village!Kategory ;
      ON  Kategory.icode = icode.icode ;
      INNER Join village!Customer ;
      ON  Simain.pcode = Customer.pcode;
      WHERE [highlight #FCE94F]Between(invdate,dt1,dt2)[/highlight] And ;
      Sisub.icode = micode ;
      INTO Cursor [highlight #FCAF3E]sijunk1[/highlight][img]https://res.cloudinary.com/engineering-com/image/upload/v1473494630/tips/lstsup_bex340.png[/img] Readwrite NoFilter
   Index On Dtoc(invdate,1) Tag invdate Additive

   Sele pcode As pcode, ;
      location, ;
      full_name,;
      sum(qntymaj) As qntymaj, ;
      sum(qntymin) As qntymin, ;
      sum(Iif(ratemin#0,qntymin*ratemin,qntymaj*ratemaj)) As amount, ;
      [highlight #FCE94F]COUNT(sino) As totsi From sijunk1[/highlight] ;
      group By pcode, location, full_name Into Curs tempsi1 Readwrite
   Alter Table tempsi1 Add Column lastsupp d
   Alter Table tempsi1 Add Column totday N(5,0)

Thanks

Saif

 

[Olaf Doschke]

That's still not a self contained example, as we don't have your data nor do I understand how your screenshot is related. I don't find the captions in your query.

But whatever, it seems you have your solution.

Bye, Olaf.

 

[Tore Bleken]

I never stop wondering how people can't understand that we are not mind readers. At minimum people should read what they write, and ask themselves "If all people know is what I have written, will they b able to understand the problem and provide useful help?

 

[alisaif]

Hi,

I simply want to calculate how many invoices have been created how much quantity in terms of Cartons or Units we have supplied and what is the value of that.

Relating to that my query is not accordingly to what I supposed to get?

Thanks

Saif

 

[Mike Lewis]

Saif,

Can you not understand our problem?

We are not sitting next to you, looking at your screen. We don't have any documentation or notes that describes your database. We don't know anything about your (or you client's) business, or the terminology that you use, other than you do some sort of invoicing.

What's more, every time we ask you for clarification, you throw some new confusion into the mix. You started by asking for a simple count of invoices. Then you said you wanted a count of invoices per period, without any indication of what constitutes a period or how it is measured. Now you are asking for invoices per carton or unit. So are these cartons or units instead of periods? Or are they somehow combined with periods? Or what.

Believe me, we do want to help you. In fact, the solution you are looking for is probably very simple. If only you could step back, and try to explain the problem clearly and unambiguously, and without assuming that we know anything at all about your system or data.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

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:

* 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

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:

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:

* 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.

 

[alisaif]

Hi,

Thanks for the time sharing for this thread. I totally understood now. And, I am really sorry I could not convey my requirement in proper way.

Thanks

Saif