• Welcome, Guest. Please login.
 
May 07, 2021, 11:15:24 PM

News:

Welcome to the SQLitening support forums!


A simple calculation, complex SQL Statement

Started by Fredrick Ughimi, January 30, 2012, 07:05:25 PM

Previous topic - Next topic

Fredrick Ughimi

Hello,

I have this scenario. Its an inventory Software I am updating. Finding better ways to do things. I have a main table (tblMainInventory), Sales table (tblSales) and a Receives table (tblReceives).  The tblMainInventory is the base table while tblSales and tblReceives interacts with tblMainInventory to always arrive at the quantity in stock at any given time.

Lets say I have:

tblMainInventory  (ProductNo1) =  40
tblSales (ProductNo1) = 30
tblReceives (ProductNo1) = 20
Balance (ProductNo1) = 30

Now, I want to be able to use the SQL statement to do the above calculation for all the products available (Not just ProductNo1 alone) in one SQL Statement. Is it possible?

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

I tried the code below wasn't giving me the correct Quantity (Balance)


slSel "SELECT distinct SUM(Quantity) as SalesQuantity FROM tblSales"
       If IsTrue slGetRow() Then
            iSalesQuantity = Val(slFN("SalesQuantity"))
       End If
       slCloseSet()
       
       slSel "SELECT distinct SUM(Quantity) as ReceivesQuantity FROM tblStoreReceives"
       If IsTrue slGetRow() Then
            iReceivesQuantity = Val(slFN("ReceivesQuantity"))
       End If
       slCloseSet()

       slSel "SELECT * FROM tblMainInventory"
           
      Do While slGetRow
            dBalance = Val(slFN("Quantity")) + iReceivesQuantity - iSalesQuantity
           
        Incr SNo
        DetailLine$ =  _
          Str$(SNo) + $Tab + _
          NigeriaDate(slFN("Date")) + $Tab + _
          slFN("ProductNo") + $Tab + _
          slFN("Description") + $Tab + _
          slFN("Brand") + $Tab + _
          slFN("Presentation") + $Tab + _ 
          slFN("UnitPack") + $Tab + _
          Format$(dBalance)
  Loop
         


Glad if some one can point to me what I am doing wrong.

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Came up with this:


slSel "Select SUM(tblMainInventory.Quantity-tblSales.Quantity+tblStoreReceives.Quantity) as Balance, * From tblMainInventory, tblSales, tblStoreReceives WHERE tblMainInventory.ProductNo=tblSales.ProductNo" & _
      "And tblMainInventory.ProductNo=tblStoreReceives.ProductNo GROUP by ProductNo" 


But I get a syntax error near tblMainInventory. Been looking can't see anything unusual.

I would glad if someone can help me spot the error.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

Yours had 3 problems:  1. No space before the And tblMain.....    2. Can't use * on this join.   3. Group by ProductNo needs qualifier.

Try this
      slSel "Select SUM(tblMainInventory.Quantity-tblSales.Quantity+tblReceive.Quantity)as Balance From tblMainInventory, tblSales, tblReceive WHERE tblMainInventory.ProductNo=tblSales.ProductNo " & _
         "And tblMainInventory.ProductNo=tblReceive.ProductNo GROUP by tblMainInventory.ProductNo" 

Fredrick Ughimi

Thanks Fred. Now I get error -13


slSel "Select *, SUM(tblMainInventory.Quantity-tblSales.Quantity+tblStoreReceives.Quantity)as Balance From tblMainInventory, tblSales, tblStoreReceives WHERE tblMainInventory.ProductNo=tblSales.ProductNo " & _
         "And tblMainInventory.ProductNo=tblStoreReceives.ProductNo GROUP by tblMainInventory.ProductNo"
       
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

>>Can't use * on this join

How do I get the other fields?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier


If you must use * then include the D ModChar.
QuoteD  = Allow duplicate column names. Not recommended if using slFN or slFNX.
            because you will always get the first value returned.  SQLite does
            not normally return qualified column names.  SQLite will return C1
            twice if you Select T1.C1, T2.C1. So the solution is to alias one of
            them with the As clause as follows Select T1.C1, T2.C1 as C1Again.
            There is a Pragma called "full_column_names" which forces SQLite to
            return qualified names, but does not seem to work if you Select *.
            Read up on it and use if you like.  I like using an alias because it
            is less code and more clear.

It would be beter to ask for each column and give them different names by using the AS clause.

Fredrick Ughimi

Something like this? I am getting AS error:


slSel "Select *, SUM(tblMainInventory.Quantity as MainQty - tblSales.Quantity as SalesQty + tblStoreReceives.Quantity as ReceivesQty) as Balance From tblMainInventory, tblSales, tblStoreReceives WHERE tblMainInventory.ProductNo=tblSales.ProductNo " & _
         "And tblMainInventory.ProductNo=tblStoreReceives.ProductNo GROUP by tblMainInventory.ProductNo"
       


Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

You can not use AS inside a function like SUM.     Replace the * with real column names including the AS clause.

Fredrick Ughimi

Hello,

No errors this time.  For testing I brought in other fields. But the result (Balance) is not correct and displays ONLY records that have the entries entered for tblMainInventory, tblSales and tblStoreReceives. For instance, If you only have entries for tblMainInventory  and tblSales this record won
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

You will need a through understanding of SLQ Joins.  The way you are doing it will only return rows if both/all tables have rows for a product.  You will probable need to do a Left Join.  Here is a link for you to get more information: http://www.w3schools.com/sql/sql_join.asp