Обсуждение: Calculating total amounts per clientid
Hi, I have an invoicing system using postgresql. Each time a customer makes a purchase, a new record is inserted into the "cart"table. The "cart" table, among other things, contains a customer_id field and a total for the amount purchased on theparticular invoice. Now what I'd like to do is to calculate the total invoices for each customer in a given time interval,then list those customers in descending order from most paying to least paying. Could someone give me some pointerson how to construct an sql statement that would flush out this data. Thanks for your help, Mark
El Sáb 13 Dic 2003 16:21, MT escribió: > Hi, > > I have an invoicing system using postgresql. Each time a customer makes a purchase, a new record is inserted into the "cart" table. The "cart" table, among other things, contains a customer_id field and a total for the amount purchased on the particular invoice. Now what I'd like to do is to calculate the total invoices for each customer in a given time interval, then list those customers in descending order from most paying to least paying. Could someone give me some pointers on how to construct an sql statement that would flush out this data. First, lets see how to get the info you want: SELECT sum(invoice) AS total, customer_id FROM cart GROUP BY cuotomer_id; Now you can order it by the total column, and maybe give it a LIMIT, in case you have to many costumers. :-) -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; ----------------------------------------------------------------- Martín Marqués | mmarques@unl.edu.ar Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral -----------------------------------------------------------------
Thanks for responding. That was the tip I needed. Regards, Mark On Sat, 13 Dec 2003 16:36:32 -0300 Martin Marques <martin@bugs.unl.edu.ar> wrote: > El Sáb 13 Dic 2003 16:21, MT escribió: > > Hi, > > > > I have an invoicing system using postgresql. Each time a customer makes a > purchase, a new record is inserted into the "cart" table. The "cart" table, > among other things, contains a customer_id field and a total for the amount > purchased on the particular invoice. Now what I'd like to do is to calculate > the total invoices for each customer in a given time interval, then list > those customers in descending order from most paying to least paying. Could > someone give me some pointers on how to construct an sql statement that would > flush out this data. > > > First, lets see how to get the info you want: > SELECT sum(invoice) AS total, customer_id FROM cart > GROUP BY cuotomer_id; > > Now you can order it by the total column, and maybe give it a LIMIT, in case > you have to many costumers. :-) > > -- > select 'mmarques' || '@' || 'unl.edu.ar' AS email; > ----------------------------------------------------------------- > Martín Marqués | mmarques@unl.edu.ar > Programador, Administrador, DBA | Centro de Telemática > Universidad Nacional > del Litoral > ----------------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend