[Solved] Adding and displaying monthly sales for salesmen

[dazedandconfused]

I have the simple query as shown in my screenshot query1. There are 2 salesmen and the query lists the total of each individual order. Salesman one has a total of $11,035 and Salesman 2 has a total of $43,700. How do I get the query to show those two totals instead of individual totals? I tried using the sum function and got the error shown in query2. Every month I want to run a query of how much the salesman has sold so I can calculate their commission.

PO invoice is the main form and PO ITEMS is my sub form if that matters at all.

[FJCC]

The query in SQL to get total sales for each person should be

Code: Select all

SELECT "SALESMAN", SUM("Order Price") FROM "PO ITEMS" GROUP BY "SALESMAN"

You can also limit that to a particular month by adding a WHERE clause, though I'm not sure which column you want to use for the date.

[Sliderule]

Since, this is an AGGREGATE query, each VISIBLE column ( field ) in the output query, . . . must contain a FUNCTION .

In your case, you want the SUM of the column "Order Price" . . . for each UNIQUE "SALESMAN" . Therefore, the FUNCTION to use for column "SALESMAN" is . . . Group .

Explanation: The above, in words means, to bring back data from the database, and, for each unique ( group ) of "SALESMAN" . . . calculate the SUM from the column "Order Price". You will probably want to include, on the ALIAS line for "Order Price" . . . something meaningful . . . as the column header output. Perhaps, Total Order Price . . . or . . . whatever you want.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this isue has been resolved.

[dazedandconfused]

Thank you very much. This is exactly what I was wondering and it worked, however it doesn't display in dollars even though the column in my table displays in dollars. Now that you mention it I would like to run the report at the beginning of the month to show sales only for the prior month. How would I do that?

[Sliderule]

You said / asked:

This is exactly what I was wondering and it worked, however it doesn't display in dollars even though the column in my table displays in dollars. Now that you mention it I would like to run the report at the beginning of the month to show sales only for the prior month. How would I do that?

Can you please post here the SQL ( Structured Query Language ) . . . that is . . . the SELECT statement . . . that you are using . . . AND . . . please include in your post . . . the TABLE and FIELD NAME that contains the DATE column.

Sliderule

[dazedandconfused]

You said / asked:

This is exactly what I was wondering and it worked, however it doesn't display in dollars even though the column in my table displays in dollars. Now that you mention it I would like to run the report at the beginning of the month to show sales only for the prior month. How would I do that?

Can you please post here the SQL ( Structured Query Language ) . . . that is . . . the SELECT statement . . . that you are using . . . AND . . . please include in your post . . . the TABLE and FIELD NAME that contains the DATE column.

Sliderule

I'm using the one FJCC gave me,

Code: Select all

SELECT "SALESMAN", SUM("Order Price") FROM "PO ITEMS" GROUP BY "SALESMAN"

The date field is in table "PO INVOICE" ,field name "Date"

I had some sensitive info, so I made a copy of the database and included it here

[Sliderule]

1. Please try this Query . . . and . . . it will prompt user to input two numbers:
   
   1. A month number ( 1 to 12 ) . . . for example . . . 9
   2. A year number as a 4 digit number . . . for example . . . 2013

   Code: Select all

   SELECT 
      "PO ITEMS"."SALESMAN",
      TO_CHAR("PO INVOICE"."Date",'MM-YYYY') as "Month", 
      SUM("PO ITEMS"."Order Price") AS "Total Order Price"
   
   FROM "PO ITEMS",
        "PO INVOICE" 
   
   WHERE "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO"
     AND MONTH("PO INVOICE"."Date") = :Enter_Month_Number
     AND YEAR("PO INVOICE"."Date") = :Enter_Year_Number_as_YYYY
   
   GROUP BY "PO ITEMS"."SALESMAN",
             TO_CHAR("PO INVOICE"."Date",'MM-YYYY')

2. Alternate Query, that will always return data ONLY from the prior month ( prior month from CURRENT_DATE ) with NO required input ( entries ) from user:
   

   Code: Select all

   SELECT 
      "PO ITEMS"."SALESMAN", 
      TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
      SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 
   
   FROM "PO ITEMS", 
        "PO INVOICE" 
   
   WHERE "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
     AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
     AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) GROUP BY "PO ITEMS"."SALESMAN", TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

I am including a copy of your database, with two Queries added by yours truly.

Copy of database.odb

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[dazedandconfused]

That's outstanding. Thank you very much. Is there a way to change it so it shows the salesman name instead of their number?

One other thing not related to a query...I tried entering data for Donald Duck just to double check since he had no sales...poor Donald. I had a problem where it's not saving the last entry very well. I enter 2 product types and for the last one I entered $200 for order price then used the navigator to move to the next record. When I came back to the one I just entered I noticed that the $200 wasn't saved however product and type and salesman were. However, if I tab 4 times to the next row or if I click on the next row below it's saved. How can I change it to save after I hit the enter key or if I tab to a new cell? Is that possible or is this just how open office operates?

Just to test it I decided to enter text for quantity, description, company, date promised, original quote price and order price in that order. The only cells that were saved when I moved to the next record and came back were product type and salesman. Odd.

Next I entered data for product type, quantity, description, company, date promised, order price and then orig quote price in that order. I moved to the next record and came back and everything was saved except orig. quote price.

[Sliderule]

Is there a way to change it so it shows the salesman name instead of their number?

I do NOT understand the 'relationship' you want / need . . . since . . . in the table "SALESMAN" . . . the Primary Key is "ID" . . . an INTEGER . . . but . . . in your other tables . . . for example . . . "PO INVOICE" and "PO ITEMS" . . . the column "Salesman" are VARCHAR . . . NOT, also INTEGER . . . therefore . . . you canNOT relate them.

I suspect . . . after you change either / both of those tables / column to an INTEGER, with the sample data . . . it should work as you want.

I strongly recommend you perform the above . . . but . . . as a TEST ( sample ) I coded the following Query . . . changing . . . in the CODE . . . the value of "PO ITEMS"."SALESMAN" to an INTEGER ( using the COALESCE function ) . . . and . . . when it ( in the table ) is NULL ( undefined ) defaulted it to an integer of 1.

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
  AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) 

GROUP BY "SALESMAN"."Salesman",
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Prompt for Month Number ( 1 to 12 ), and Year Number ( as 4 digit number ):

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND MONTH( "PO INVOICE"."Date" ) = :Enter_Month_Number 
  AND YEAR( "PO INVOICE"."Date" ) = :Enter_Year_Number_as_YYYY 

GROUP BY "SALESMAN"."Salesman", 
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Copy of database.odb

I do not understand your other question / comment . . . since I do NOT know where / how you entered the data. Also . . . it has nothing to do with this original post . . . so . . . if necessary . . . please start another forum topic.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[dazedandconfused]

OK. Thanks. I'll take a look at it again tomorrow.

[dazedandconfused]

Is there a way to change it so it shows the salesman name instead of their number?

I do NOT understand the 'relationship' you want / need . . . since . . . in the table "SALESMAN" . . . the Primary Key is "ID" . . . an INTEGER . . . but . . . in your other tables . . . for example . . . "PO INVOICE" and "PO ITEMS" . . . the column "Salesman" are VARCHAR . . . NOT, also INTEGER . . . therefore . . . you canNOT relate them.

I suspect . . . after you change either / both of those tables / column to an INTEGER, with the sample data . . . it should work as you want.

I strongly recommend you perform the above . . . but . . . as a TEST ( sample ) I coded the following Query . . . changing . . . in the CODE . . . the value of "PO ITEMS"."SALESMAN" to an INTEGER ( using the COALESCE function ) . . . and . . . when it ( in the table ) is NULL ( undefined ) defaulted it to an integer of 1.

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND "PO INVOICE"."Date" >= CASE WHEN MONTH( CURRENT_DATE ) = 1 THEN ( CAST( YEAR( CURRENT_DATE ) - 1 || '-12-01' AS DATE ) ) ELSE CAST( YEAR( CURRENT_DATE ) || '-' || RIGHT( '0' || MONTH( CURRENT_DATE ) - 1, 2 ) || '-01' AS DATE ) END 
  AND "PO INVOICE"."Date" < CAST( TO_CHAR( CURRENT_DATE, 'YYYY-MM-01' ) AS DATE ) 

GROUP BY "SALESMAN"."Salesman",
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Prompt for Month Number ( 1 to 12 ), and Year Number ( as 4 digit number ):

Code: Select all

SELECT 
   "SALESMAN"."Salesman", 
   TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' ) AS "Month", 
   SUM( "PO ITEMS"."Order Price" ) AS "Total Order Price" 

FROM "PO ITEMS", 
     "PO INVOICE", 
     "SALESMAN" 

WHERE CAST(COALESCE("PO ITEMS"."SALESMAN",1) as INTEGER) = "SALESMAN"."ID"
  AND "PO INVOICE"."ID" = "PO ITEMS"."FK_ID_PO" 
  AND MONTH( "PO INVOICE"."Date" ) = :Enter_Month_Number 
  AND YEAR( "PO INVOICE"."Date" ) = :Enter_Year_Number_as_YYYY 

GROUP BY "SALESMAN"."Salesman", 
         TO_CHAR( "PO INVOICE"."Date", 'MM-YYYY' )

Copy of database.odb

I do not understand your other question / comment . . . since I do NOT know where / how you entered the data. Also . . . it has nothing to do with this original post . . . so . . . if necessary . . . please start another forum topic.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

All my primary keys are INTEGER'S. Salesman is a VARCHAR in all tables. I don't understand why I'd change it to an integer? I wouldn't be able to enter their name that way would I?

[Sliderule]

You said / asked:

All my primary keys are INTEGER'S. Salesman is a VARCHAR in all tables. I don't understand why I'd change it to an integer? I wouldn't be able to enter their name that way would I?

A Primary Key does NOT have to be an INTEGER, that is NOT a requirement.

I thought, you were developing a Relational Database . . . where . . . you do NOT duplicate data. That is why, if you RELATE the "Salesman" ( or is it "SALESMAN" . . . I mean . . . you are consistent in the name . . . that is . . . consistently inconsistent . . . sometimes "Salesman", somtimes "SALESMAN" ) . . . to the ID of the appropriate table . . . you only enter the NAME ( full text name ) once, and, if / when a name changes . . . only ONE change in the appropriate table is needed. And, this way, if you misspelled . . . for example . . . 'DONALD DUC' rather than 'DONALD DUCK' . . . the problem would never occur.

This is all part of designing a Normalized Relational Database.

Bottom line, creating a well designed database, is just like, constructing a building , , , a large skyscraper. If it, the building, is NOT set on a firm foundation, the building will collapse. Likewise, if your database is not constructed as a normalized database, it will either collapse, or, cause ridiculous permutations to get what you want from it.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[dazedandconfused]

Thanks. I can't figure out how to have it display USD instead of a number. Is there a way to do that?

[Sliderule]

Thanks. I can't figure out how to have it display USD instead of a number. Is there a way to do that?

USD . . . ¿ what is that ?

The way any Number ( prefix, thousands separator, number of decimal places, choice of character representing a decimal place, etc ), Date, Time, or Boolean field is displayed ( shows up on a screen ) depends on how you FORMAT the output data ( column ), whether a Result Set from a Query, a Form, or, a Report.

Sliderule

[dazedandconfused]

US dollars. How do I set the format of us dollars?

[Sliderule]

You have NOT told me what your output is . . . a Query, a Form, a Report, other?

Sliderule

[dazedandconfused]

You have NOT told me what your output is . . . a Query, a Form, a Report, other?

Sliderule

My apologies. When I run a query or a report I would like the column "Total Order Price" to be shown in US dollars instead of as a number

[Sliderule]

The way any Number ( prefix, thousands separator, number of decimal places, choice of character representing a decimal place, etc ), Date, Time, or Boolean field is displayed ( shows up on a screen ) depends on how you FORMAT the output data ( column ), whether a Result Set from a Query, a Form, or, a Report.

1. So, if it is a Query, after running the Query and you have a Result Set, format the column "Total Order Price" the way you want. For instance:
   1. Right click on the output column, "Total Order Price"
   2. From the pop-up, choose: Column Format...
   3. Click on the Format tab button
   4. Under Category, click on Currency
   5. Make any additional modifications, if needed
   6. If you desire to change the Alignment, Click on the Alignment tab button
   7. Make any modifications you desire
   8. Press the OK button
2. For a report, format the field in the report as you desire.

Sliderule

[dazedandconfused]

Thank you. What I want to do is have it set to us dollars by default every time I run the query or when I run the report. Is that possible?

[Sliderule]

What I want to do is have it set to us dollars by default every time I run the query or when I run the report. Is that possible?

With the current OpenOffice / LibreOffice Base front end, the format output of a Query must be made each time the Query is run.

However, when displaying a Table, or a View, the format of the fields are stored as a part of the database front-end in the OpenOffice / LibreOffice Base file ( *.odb ) . . . therefore . . . you can create a View of the Query, save the format, and merely display that View.

This is the last time I will repeat myself, and, just to make sure you can see it . . . I will display ( format the output ) it with big letters:

For a report, format the field in the report as you desire.

Sliderule

[dazedandconfused]

Thanks again