M. Anderson M. Anderson - 1 month ago 8
SQL Question

Incorrect syntax near the keyword 'GROUP' - MA

I am trying to get all of my inventory movements into either a 2015 or 2016 bucket. In my From Select part of the query, I get it to work but it gives me 2 lines for each item with a qty in 2015 and 0 in 2016 on the first line and a 0 in 2015 and a qty in 2016 on the second line. So I thought I could then query the select statement and group to get one line for each item. But I am getting an "Incorrect syntax near the keyword 'GROUP'" error. Can someone get me going in the right direction? The error occurs on the second Group By outside the From clause. Thanks.

SELECT [Item]
,[Item Desc]
,sum[2015 Usage]
,sum[2016 Usage]

FROM (SELECT
mtm.[item] 'Item'
,im.[description] 'Item Desc'
,case when year(convert(varchar(10), convert(date,mtm.[trans_date],1),101))='2015' then sum(mtm.[qty]) else 0 end '2015 Usage'
,case when year(convert(varchar(10), convert(date,mtm.[trans_date],1),101))='2016' then sum(mtm.[qty]) else 0 end '2016 Usage'

FROM [ITEC_App].[dbo].[matltran_mst] mtm, [ITEC_App].[dbo].[item_mst] im

WHERE
mtm.[item] = im.[item]
and
mtm.[trans_type] NOT IN ('A', 'B', 'C', 'F', 'G', 'H', 'M', 'N', 'R')
and
mtm.[ref_type] NOT IN ('P')

GROUP BY
mtm.[item]
,im.[description]
,year(convert(varchar(10), convert(date,mtm.[trans_date],1),101)))

GROUP BY
[Item]
,[Item Desc]

Answer

Your subquery is like this:

  (
  SELECT . . .
  GROUP BY
    mtm.[item]
    ,im.[description]
    ,year(convert(varchar(10), convert(date,mtm.[trans_date],1),101))
 )

In SQL Server, all subqueries require a table alias. So, you just need to add one:

  (
  SELECT . . .
  GROUP BY
    mtm.[item]
    ,im.[description]
    ,year(convert(varchar(10), convert(date,mtm.[trans_date],1),101))
 ) i