Sum the details in data base

Question

1.00/5 (1 vote)

See more:

I've table called Stock details which has columns like
date/factory1/factory2/factory3/total/month_total/year_total.
{Total is factory1+factory2+factory3}
{month_total is stock details from the 1st month to the current date of that month}
{year_total is stock details from the 1st jan to the current date}
So the problem here is how do i write query to calculate year n month total
Please help!!
Thank you☻☻☺☺☺

Posted 28-Aug-12 19:36pm

Sharath2790

Updated 28-Aug-12 19:58pm

v2

Add a Solution

2 solutions

Solution 2

if your table contians column which having month total and year total.
then you can use that values only...

try below query..

SQL

select datepart(year,dt) yr, datepart(mm,dt) month, month_tot,year_tot 
from users 
where dt in (select max(dt) from Stock_details 
             group by datepart(year,dt),
                datepart(mm,dt))
order by 1 asc,2 asc,3 asc

Posted 28-Aug-12 20:19pm

ssd_coolguy

Updated 28-Aug-12 20:47pm

v2

Comments

Sharath2790 29-Aug-12 2:39am

Your solution may work but i can't keep updating the year and month part because this database will be connected to vb front end or i can't keep asking the user to enter which month/year is this..

ssd_coolguy 29-Aug-12 2:43am

means you want month & year wise data right?

Sharath2790 29-Aug-12 2:45am

yep!!

ssd_coolguy 29-Aug-12 2:48am

see my updated solution...

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-28T19:50:00

Solution 1

SQL

select monthtotal+yeartotal_id as total from stockdetails

updated solution:

C#

select sum(total) as month_Total from stockdetails where day(date)<=day(Getdate()) and month(date)=month(getdate()) and year(Date)=year(getdate())

The above query will give me sum of total column for the particular month 1st to current date.

C#

select sum(total) as year_total from stockdetails where day(date)<=day(Getdate()) and month(date)<=month(getdate()) and year(Date)=year(getdate())

this will give you years total

Posted 28-Aug-12 19:50pm

Santhosh Kumar Jayaraman

Updated 28-Aug-12 20:21pm

v3

Comments

Sharath2790 29-Aug-12 1:55am

Sorry!! that doesn't work. probably you failed to understand my question! I need to add stocks for whole month in month_total and add stocks for whole year in year_total based on dates and i'm not using any id here!!

Santhosh Kumar Jayaraman 29-Aug-12 2:03am

Probably you didnt explain this first time when you posted the question. Aint i right? and you are downvoting answer for your mistake!!

Sharath2790 29-Aug-12 2:20am

chill!!

Santhosh Kumar Jayaraman 29-Aug-12 2:10am

check my updated solution

Sharath2790 31-Aug-12 1:38am

Hey! Santhosh Can you help me with latest query?? Please

Santhosh Kumar Jayaraman 31-Aug-12 1:50am

latest? I didnt get you

Sharath2790 31-Aug-12 1:51am

http://www.codeproject.com/Questions/450538/Retrieve-last-updated-on-Database CHECK THIS!!!!

Sum the details in data base

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Sum the details in data base

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us