If we group by Account Number then we get a slightly different result. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo ,SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber GROUP BY InvoiceNo In the example below we are grouping by invoice number (InvoiceNo), since there are 4 distinct invoice numbers there are four line items in the query result If you want a query with an aggregate function to deliver multiple rows such as by invoice number or account number then you must use the GROUP BY clause and the appropriate column to group on. All the line items were added together and incorrectly attributed to one customer. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo, SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber Well we could try to use the SUM aggregate function as we did to calculate the total value of the inventory. Let us say that we want to know what each customer spent. Sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo,ItemNo,Price,Quan ,(Price*Quan) 'EXT' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber Be very careful in using Natural Join queries in the absence of properly matched columns, a cartesian product will be produced. AcctNumber Ī NATURAL JOIN will also work in the above example since the primary key and the foreign key in the two tables have the same name.
#SQLITE INNER JOIN ORDER BY CODE#
The following code will achieve the same result. The above query by the way is known as in Inner Join query where the only rows returned are ones in which both tables have fields that match the stated criteria. Sqlite> SELECT DISTINCT Customers.AcctNumber, Customers.Custname FROM Customers,Ĭust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber Using the "SELECT DISTINCT" clause as shown below, enables us to eliminate the duplicate rows. However we are only interested in just a list of customers that have ordered from us. If they had 50 invoice numbers under the same account number then they would be listed 50 times. That they are listed in the results twice.
has two invoice numbers associated with the same account number in the "cust_invoice" table Sqlite> SELECT Customers.AcctNumber, Customers.Custname FROM Customers, cust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber In the Customers table with the foreign key field "AcctNumber" in the cust_invoice table To find this out we can relate the table listing the customer accounts with the table listing the invoices by matching the primary key field "AcctNumber" Let us say that we want a list of customers that have ordered from us in the past. "catalogsales" which is a detail table for "cust_invoice" listing the individual items ordered by our customers on each invoice using the invoice number "InvoiceNo" as a foreign key. Here we have created three tables, one a list of customers and their Account numbers called oddly enough, "Customers"Ī table called "cust_invoice" listing invoices and using the customer account number as a foreign key with the "Customers" table.
0 kommentar(er)
