SQL - Sum Aggregate function

Question

I am fairly new to SQL, so please bare that in mind.

I have have a table with data that is divided into two centers "Toronto & Montreal" for a total of 78 rows (39 per center), with multiple columns. I want to get the national total (sum) of X column for each respective center for a specific daterange (month). For example the Jan 2018 total of full time employees between Montreal + Toronto Centers combined. The logic statement for example would be to add Toronto + Montreal Jan 2018 results to yield me the national total. Although with my beginner skills I am having trouble writing a sql query that can be executed without syntax error.

select sum(fte), daterange, center
from (
select  'Toronto' as Center,
        sum(fte) as total fte
from dbo.example
where daterange = '2015-11-01'
group by total fte

union 

select  'Montreal' as Center,
        sum(fte) as total fte
from dbo.example
where daterange = '2015-11-01'
group by total fte
)temptable
group by total fte

The above query is giving me a error "Incorrect syntax near 'fte'." fte is a column within my table.

Please advise me what I am doing wrong.

Cheers!

You typically GROUP BY the columns you select, except those who are arguments to set functions (e.g. SUM.) — jarlh
– jarlh, Commented Mar 9, 2018 at 15:12
No need for that UNION. Simple use WHERE to pick the centers. Then GROUP BY center! — jarlh
– jarlh, Commented Mar 9, 2018 at 15:13
The query makes no sense . . . "Toronto" and "Montreal" woul dhave the same values. Also, your table name is dbo.example; the dbo schema is usually associated with SQL Server and not MySQL. — Gordon Linoff
– Gordon Linoff, Commented Mar 9, 2018 at 15:14

James · Accepted Answer · 2020-04-11 00:26:50Z

3

The query should look like this:

select Center,
  sum(fte) as "[total fte]"
from dbo.example
where daterange = '2015-11-01'
  and Center in ('Toronto','Montreal')
group by Center

Understanding that in the attribute Center exist these Strings.

edited Apr 11, 2020 at 0:26

answered Mar 9, 2018 at 15:16

James

3,0133 gold badges15 silver badges26 bronze badges

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

1 Comment

shawnt00 Over a year ago

Just put some quotes around that column alias.

Pat · Accepted Answer · 2018-03-09 15:35:46Z

0

select Center,
  sum(convert(float,fte)) as total_fte
from dbo.example
where daterange = '2016-11-01'
  and Center in ('Toronto','Montreal')
group by Center

Works ^ thanks everyone

answered Mar 9, 2018 at 15:35

Pat

1134 silver badges12 bronze badges

1 Comment

shawnt00 Over a year ago

You should probably about floats especially since I think these are dollar amounts.

Collectives™ on Stack Overflow

SQL - Sum Aggregate function

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related