wood burning stoves 2.0*
The moose likes JDBC and the fly likes Get SUM of product quantity for product 'A' of Company 'XYZ' from database monthwise for whole year. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Get SUM of product quantity for product Watch "Get SUM of product quantity for product New topic
Author

Get SUM of product quantity for product 'A' of Company 'XYZ' from database monthwise for whole year.

Mayur Saparia
Greenhorn

Joined: Feb 13, 2013
Posts: 19

I have a table which has product quantity column and the database has multiple entries in a single month. For eg: Date is in string format as i have used JXDatePicker so i was unable to store date in Date type in mySql . Date format is (yyyy-MM-dd) 2013-03-01 > 1200 (This is product quantity) 2013-03-05 >200

2013-04-05 > 500 2013-04-10 > 1000

2013-05-05 > 850 2013-05-10 > 50

so i want data as

for March(2013-03-01 to 2013-03-31) > SUM OF product i.e : 1400. April (2013-04-01 to 2013-04-30) > SUM OF product i.e : 1500. May (2013-05-01 to 2013-05-31) > SUM OF product i.e : 900.

also i have different kind of propducts so i will be getting it according to product , For example purpose consider this records are ONLY FOR Product 'A' of Customer 'XYZ', So please consider this in where clause.

I have tried getting the data month wise using query as follows:

select * from mm_inward Where inward_customerId=1 AND inward_productId=1 ORDER BY FROM_UNIXTIME(inward_dateofdc) ASC

and i get the data as follows

the date format have changed because I have pasted it from Excel but please keep the date format as mentioned above.

This is the actual data from my database where i have 2 entries of March 3/2/2013 > 1500 & 3/14/2013 > 120 so i want the sum of it and not differently displaying both the records.


Thanks In advance

Regards
Mayur.
T Mishra
Ranch Hand

Joined: Apr 04, 2006
Posts: 108

I ain't an expert in sql, but you may try this approach.

1. find distinct month, year from 'Date of deleivery reciept'
2. for each item, calculate sum of 'Product quantity' in the results above.
3. group by Product Name

It worked for me but perhaps you may want the advice of sql experts.

Thanks,
Tushar (SCJP 1.5)
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1609
    
  13

You can fetch the data you want in a single SELECT statement if you use a SQL GROUP BY to aggregate the data by company/product/month. If I were you, I'd use the actual month, so you will need to:

(a) convert your string to a proper date
(b) extract the month from that date
(c) GROUP BY the company, product and month
(d) SUM the quantity

See the MySQL website for various date-related functions to do this.

Of course, you could just substring your date string to get the month, but frankly I think you should suffer a little inconvenience for not storing your date values properly in the first place. If you store dates as strings then you prevent anybody else from doing useful date arithmetic on them easily via SQL, as they always have to convert your strings to a proper date first. This is inefficient and unnecessary. Store dates as dates.

No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Get SUM of product quantity for product 'A' of Company 'XYZ' from database monthwise for whole year.
 
Similar Threads
how to display all months of a particular year according to the following rule
Independently configure date format for XML/JSON serialization
Linking and organizing combo boxes
Hibernate: one-to-one Different Composite Keys
Get Sum of column and the retrive the data month wise.