How to cherry pick from datatool DB and calculate totals

Question

I have a CSV file with current prices/item. These prices are updated regularly.

I have loaded a DB with datatool. I would like to use these prices to calculate totals according to quantities I have in my LaTeX file.

My price.csv has is as such:

item, price
A, 0.1
B, 0.03
C, 1.2

Now I would like to have a table where I have my quantities and totals as follows:

Item     Quantity     Total

 A        100          10   
 B        5            0.15 
 C        1            1.2

 Total    106          11.53

Where all totals are calculated as the values in price.csv tend to change.

Answer 1

datatool provides a number of functions to access and process database content. Calculations are easily performed using fp-notation:

\documentclass{article}
\usepackage{datatool,filecontents}% http://ctan.org/pkg/{datatool,filecontents}

\newcommand{\resetgrandtotals}{\gdef\grandtotal{0}\gdef\totalcount{0}}
\newcommand{\itemtotal}[2]{% \itemtotal{<item>}{<quantity>}
  \DTLgetvalueforkey{\result}{price}{price}{item}{#1}% Extract data from database
  \FPeval\result{trunc(\result*#2:2)}\result% Calculate total & display
  \FPeval\totalcount{trunc(\totalcount+#2:0)}% Add to total count
  \FPeval\grandtotal{trunc(\grandtotal+\result:2)}% Add to grand total
  \xdef\grandtotal{\grandtotal}% Make definition global
  \xdef\totalcount{\totalcount}% Make definition global
}
\newcommand{\itemprice}[1]{% \itemprice{<item>}
  \DTLgetvalueforkey{\result}{price}{price}{item}{#1}\result% Extract data from database
}
\begin{filecontents*}{price.csv}
item, price
A, 0.1
B, 0.03
C, 1.2
\end{filecontents*}
\begin{document}
\DTLloaddb{price}{price.csv}% Load database
\DTLdisplaydb{price}% Display database

\resetgrandtotals
\begin{tabular}{lrr}
  Item  &    Quantity & Total \\
  \hline
  A     &         100 & \itemtotal{A}{100} \\ % 10.00
  B     &           5 & \itemtotal{B}{5}   \\ %  0.15
  C     &           1 & \itemtotal{C}{1}   \\ %  1.20
  Total & \totalcount & \grandtotal           % 11.53
\end{tabular}

The price associated with~B is \itemprice{B}.
\end{document}

The above MWE provides \itemtotal{<item>}{<quantity>} which prints the item total based on the quantity and price (the latter extracted from the database using \DTLgetvalueforkey). Additionally it tallies a \totalcount and \grandtotal for use in the table at a later stage. \itemprice{<item>} is used as a wrapper for \DTLgetvalueforkey to return the price associated with item <item>.

Resetting of the totals is required and achieved using \resetgrandtotals.