calculate a total using SQL Query

Question

I have a table with 3 attributes:

Title          TotalNumberOfAuthors              TotalNumberOfPublishedAuthors
A                    3                                         1
B                    2                                         2
C                    4                                         2
D                    2                                         1

I am trying to create a query that Displays the Sum of TotalNumberOfPublishedAuthors and the sum of TotalNumberOfNonPublishedAuthors which is (TotalNumberofAuthors - TotalNumberOfPublishedAuthors)..

This is the query that I have generated, but doesn't display the results as expected:

SELECT SUM(Submission.TotalNumberOfPublishedAuthors),        
       (SUM(Submission.TotalNumberOfAuthors) - SUM(Submission.TotalNumberOfPublishedAuthors)) AS Number_of_Non_Published_Authors
FROM Submission INNER JOIN ((Faculty INNER JOIN School 
ON Faculty.FacultyID = School.[FacultyID]) INNER JOIN (Researcher INNER JOIN ResearcherSubmission 
ON Researcher.ResearcherID = ResearcherSubmission.ResearcherID) 
ON School.SchoolID = Researcher.SchoolID) 
ON Submission.SubmissionID = ResearcherSubmission.SubmissionID;

This is the result I am trying to get:

TotalNumberofPublishedAuthors                     TotalNumberofPublishedAuthors
         6                                                     5

Your question is confusing. You say you have a table, then you show a query with multiple tables. Can you set up a SQL Fiddle with some sample data? — Gordon Linoff
– Gordon Linoff, Commented Sep 28, 2015 at 10:25
Also it would help to show the value that is being returned, or is the SQL just not working? — MiguelH
– MiguelH, Commented Sep 28, 2015 at 10:27
Looking at the query you have used this should give you the result as you expect. SUM(Submission.TotalNumberOfPublishedAuthors), (SUM(Submission.TotalNumberOfAuthors) - SUM(Submission.TotalNumberOfPublishedAuthors)) But as per you the result is different, what are you getting? — Abhishek
– Abhishek, Commented Sep 28, 2015 at 10:36
it looks like some problem with your join operation. check whether you are getting all row or not..... if you are getting all rows with your join, then definitely will get you correct result as you expected. — Indra Prakash Tiwari
– Indra Prakash Tiwari, Commented Sep 28, 2015 at 13:19

t-clausen.dk · Accepted Answer · 2015-09-28 10:54:08Z

1

SELECT <Column Names>
FROM <Table Name>
UNION ALL
SELECT NULL,SUM(<Column name>),null,sum(<RunningTotal or AnyTotal>)
FROM <Table Name>

edited Sep 28, 2015 at 10:54

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

answered Sep 28, 2015 at 10:38

user5184173

Sign up to request clarification or add additional context in comments.

Comments

Paul Maxwell · Accepted Answer · 2015-10-02 03:31:48Z

If you really have columns in the table [Submission] called [TotalNumberOfPublishedAuthors] and [TotalNumberOfAuthors] as shown then there is no need to join any other tables. Just perform the sums as you already have them in the query but without further tables.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Submission
    ([Title] varchar(1), [TotalNumberOfAuthors] int, [TotalNumberOfPublishedAuthors] int)
;

INSERT INTO Submission
    ([Title], [TotalNumberOfAuthors], [TotalNumberOfPublishedAuthors])
VALUES
    ('A', 3, 1),
    ('B', 2, 2),
    ('C', 4, 2),
    ('D', 2, 1)
;

Query 1:

SELECT
      SUM(Submission.TotalNumberOfPublishedAuthors) AS TotalNumberOfPublishedAuthors
    , SUM(Submission.TotalNumberOfAuthors) 
    - SUM(Submission.TotalNumberOfPublishedAuthors) AS Number_of_Non_Published_Authors
FROM Submission

Results:

| TotalNumberOfPublishedAuthors | Number_of_Non_Published_Authors |
|-------------------------------|---------------------------------|
|                             6 |                               5 |

Collectives™ on Stack Overflow

calculate a total using SQL Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related