how to do the sum of a particular column ?

Question

0.00/5 (No votes)

See more:

hello ... i want to calculate the sum of the amount ... but i am getting a blank column when i run in ms-access sql view and nothing appears when i use this query in software.

SQL

SELECT AllocateAsset.Item, AllocateAsset.ItemModel, Count(AllocateAsset.ItemModel) AS CountOfItem, (CountOfItem*(SELECT Rate FROM rateList  WHERE rateList.MakeModel=AllocateAsset.ItemModel ))/4 AS Amount, SUM(Amount) AS Total

FROM aboutPM INNER JOIN (AllocateAsset INNER JOIN PMReport ON AllocateAsset.ID=PMReport.AssetID) ON aboutPM.ID=PMReport.AboutPMID

WHERE (((aboutPM.Year)="2015") AND ((aboutPM.Period)="Jan-Feb-Mar"))

GROUP BY AllocateAsset.Item, AllocateAsset.ItemModel

ORDER BY AllocateAsset.Item;

please help me out

Posted 30-Sep-15 20:01pm

Akshay Shringi

Updated 30-Sep-15 20:17pm

Naveen.Sanagasetti

v2

Add a Solution

Comments

Naveen.Sanagasetti 1-Oct-15 2:19am

SELECT Rate FROM rateList WHERE rateList.MakeModel=AllocateAsset.ItemModel

Please check the result set of this...

Akshay Shringi 1-Oct-15 2:23am

after removing sum(Amount) As total ... the query works fine

Naveen.Sanagasetti 1-Oct-15 5:04am

Enjoy :)

Akshay Shringi 1-Oct-15 5:56am

but i need to do the sum of the column Amount ... and i am not getting it

Jörgen Andersson 1-Oct-15 8:27am

You can't refer to the result from one column in another column. You need to use a CTE or a subquery.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2015-10-01T21:41:00

Solution 1

Try this:

SQL

SELECT t1.*, SUM(t1.Amount) AS Total
FROM (
    SELECT AllocateAsset.Item, AllocateAsset.ItemModel, Count(AllocateAsset.ItemModel) AS CountOfItem,
        (CountOfItem*(SELECT Rate FROM rateList  WHERE rateList.MakeModel=AllocateAsset.ItemModel ))/4 AS Amount
    FROM aboutPM INNER JOIN 
        (AllocateAsset INNER JOIN 
            PMReport ON AllocateAsset.ID=PMReport.AssetID) ON aboutPM.ID=PMReport.AboutPMID
    WHERE (((aboutPM.Year)="2015") AND ((aboutPM.Period)="Jan-Feb-Mar"))
    GROUP BY AllocateAsset.Item, AllocateAsset.ItemModel
    ORDER BY AllocateAsset.Item;
) AS t1
GROUP BY t1.Item, t1.ItemModel, t1.CountOfItem

Does Year field is varchar data type? It should be numeric(integer) data type!

Posted 1-Oct-15 21:41pm

Maciej Los

Comments

Akshay Shringi 3-Oct-15 6:22am

yes year is a integer datatype

Akshay Shringi 3-Oct-15 6:23am

what is t1 ... have you made a table form this query ? its say error in FROM clause

Maciej Los 3-Oct-15 17:01pm

t1 is an alias for subquery. Please, correct this: SELECT t1.*, SUM(t1.Amount) AS Total. There was a bug when i posted my answer.
If Year is numeric field, change this part of query as follow: (aboutPM.Year)=2015

Akshay Shringi 5-Oct-15 1:12am

error in From Clause ... does i have to make the query as a table t1 ?

Maciej Los 5-Oct-15 1:46am

No!
Please, try this

SELECT t1.Item, t1.ItemModel, t1.CountOfItem, (t1.Amount) As Total
FROM (
--subquery here!
) AS t1
GROUP BY ...

Akshay Shringi 5-Oct-15 2:06am

nope ... not running ... not include the specified expression'Amount ' as Part of aggregate function