By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,812 Members | 1,978 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,812 IT Pros & Developers. It's quick & easy.

Calculating total amounts per clientid

P: n/a
MT
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.

Thanks for your help,

Mark

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a
MT
Thanks for responding. That was the tip I needed.

Regards,

Mark

On Sat, 13 Dec 2003 16:36:32 -0300
Martin Marques <ma****@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 | mm******@unl.edu.ar
Programador, Administrador, DBA | Centro de Telemática
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.