Incorrect syntax near the keyword 'Plan'.

Question

Post reply

Incorrect syntax near the keyword 'Plan'.

yrstruly

Hall of Fame

Points: 3475

May 1, 2023 at 3:24 pm

#4182406

I am trying to create the SQL that gives this: Untitled

The SQL I have:

SELECT 
  p.name AS Plan,
  COUNT(s.id) AS Count,
  SUM(t.amount) AS [Amount ($) ]
FROM 
  subscriptions s
JOIN 
  plans p ON s.plan_id = p.id
JOIN 
  transactions t ON s.id = t.subscription_id
WHERE 
  s.status = 'SUBSCRIBED:ACTIVE'
GROUP BY 
  p.name
ORDER BY 
  p.name ASC

Error: error

DDL:


/****** Object:  Table [dbo].[Subscriptions]    Script Date: 2023/05/01 17:22:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Subscriptions](
[ID] [float] NULL,
[Owner ID] [float] NULL,
[Status] [nvarchar](255) NULL,
[Replaced By] [nvarchar](255) NULL,
[Plan ID] [float] NULL,
[Payment First] [nvarchar](255) NULL,
[Payment Last] [nvarchar](255) NULL,
[Payment Valid Till] [nvarchar](255) NULL,
[Discount Coupon ID] [float] NULL,
[Discount Invite ID] [nvarchar](255) NULL,
[Discount Ppp] [nvarchar](255) NULL,
[Gateway] [nvarchar](255) NULL,
[Gateway Sub ID] [nvarchar](255) NULL,
[Amount] [float] NULL,
[Amount Before Discount] [float] NULL,
[Metadata] [nvarchar](255) NULL,
[Bill Attempts] [float] NULL,
[Bill Attempt At] [nvarchar](255) NULL,
[Synced At] [nvarchar](255) NULL,
[Downgraded At] [nvarchar](255) NULL,
[Created At] [nvarchar](255) NULL,
[Updated At] [nvarchar](255) NULL
) ON [PRIMARY]
GO


--------


/****** Object:  Table [dbo].[Transactions]    Script Date: 2023/05/01 17:22:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Transactions](
[ID] [float] NULL,
[User ID] [float] NULL,
[Type] [nvarchar](255) NULL,
[Status] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Paid At] [nvarchar](255) NULL,
[Course ID] [float] NULL,
[Subscription ID] [nvarchar](255) NULL,
[Invoice ID] [nvarchar](255) NULL,
[Gateway] [nvarchar](255) NULL,
[Gateway Transaction ID] [nvarchar](255) NULL,
[Amount] [float] NULL,
[Amount Before Discount] [nvarchar](255) NULL,
[Amount Refunded] [float] NULL,
[Discount Coupon ID] [nvarchar](255) NULL,
[Discount Invite ID] [nvarchar](255) NULL,
[Discount Ppp] [nvarchar](255) NULL,
[Referral] [nvarchar](255) NULL,
[Refund From] [nvarchar](255) NULL,
[Metadata] [nvarchar](255) NULL,
[Created At] [nvarchar](255) NULL,
[Updated At] [nvarchar](255) NULL,
[Deleted At] [nvarchar](255) NULL
) ON [PRIMARY]
GO

-------


/****** Object:  Table [dbo].[Plans]    Script Date: 2023/05/01 17:23:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Plans](
[ID] [float] NULL,
[Paypal Ids â†’ 109500] [nvarchar](255) NULL,
[Paypal Ids â†’ 11200] [nvarchar](255) NULL,
[Paypal Ids â†’ 112500] [nvarchar](255) NULL,
[Paypal Ids â†’ 11400] [nvarchar](255) NULL,
[Paypal Ids â†’ 1200] [nvarchar](255) NULL,
[Paypal Ids â†’ 14700] [nvarchar](255) NULL,
[Paypal Ids â†’ 15000] [nvarchar](255) NULL,
[Paypal Ids â†’ 150000] [nvarchar](255) NULL,
[Paypal Ids â†’ 16500] [nvarchar](255) NULL,
[Paypal Ids â†’ 17500] [nvarchar](255) NULL,
[Paypal Ids â†’ 175000] [nvarchar](255) NULL,
[Paypal Ids â†’ 18000] [nvarchar](255) NULL,
[Paypal Ids â†’ 1875] [nvarchar](255) NULL,
[Paypal Ids â†’ 19000] [nvarchar](255) NULL,
[Paypal Ids â†’ 195000] [nvarchar](255) NULL,
[Paypal Ids â†’ 2000] [nvarchar](255) NULL,
[Paypal Ids â†’ 22500] [nvarchar](255) NULL,
[Paypal Ids â†’ 234000] [nvarchar](255) NULL,
[Paypal Ids â†’ 2500] [nvarchar](255) NULL,
[Paypal Ids â†’ 3021000] [nvarchar](255) NULL,
[Paypal Ids â†’ 3035000] [nvarchar](255) NULL,
[Paypal Ids â†’ 40000] [nvarchar](255) NULL,
[Paypal Ids â†’ 414000] [nvarchar](255) NULL,
[Paypal Ids â†’ 4900] [nvarchar](255) NULL,
[Paypal Ids â†’ 5000] [nvarchar](255) NULL,
[Paypal Ids â†’ 5800] [nvarchar](255) NULL,
[Paypal Ids â†’ 5900] [nvarchar](255) NULL,
[Paypal Ids â†’ 665000] [nvarchar](255) NULL,
[Paypal Ids â†’ 7200] [nvarchar](255) NULL,
[Paypal Ids â†’ 7500] [nvarchar](255) NULL,
[Paypal Ids â†’ 75000] [nvarchar](255) NULL,
[Paypal Ids â†’ 782000] [nvarchar](255) NULL,
[Paypal Ids â†’ 79000] [nvarchar](255) NULL,
[Paypal Ids â†’ 792000] [nvarchar](255) NULL,
[Paypal Ids â†’ 825000] [nvarchar](255) NULL,
[Paypal Ids â†’ 850] [nvarchar](255) NULL,
[Paypal Ids â†’ 8900] [nvarchar](255) NULL,
[Paypal Ids â†’ 900] [nvarchar](255) NULL,
[Paypal Ids â†’ 950] [nvarchar](255) NULL,
[Paypal Ids â†’ 950000] [nvarchar](255) NULL,
[Paypal Ids â†’ 9800] [nvarchar](255) NULL,
[Slug] [nvarchar](255) NULL,
[Type] [nvarchar](255) NULL,
[Order] [float] NULL,
[Name] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Icon] [nvarchar](255) NULL,
[Billing Text] [nvarchar](255) NULL,
[Cycle] [nvarchar](255) NULL,
[Cycle Interval] [float] NULL,
[Price] [float] NULL,
[Price Before] [float] NULL,
[Price Setup] [float] NULL,
[Trial Cycle] [nvarchar](255) NULL,
[Trial Cycle Interval] [float] NULL,
[Activated At] [nvarchar](255) NULL,
[Deactivate At] [nvarchar](255) NULL,
[Synced At] [nvarchar](255) NULL,
[Created At] [nvarchar](255) NULL,
[Updated At] [nvarchar](255) NULL,
[Visible] [bit] NULL
) ON [PRIMARY]
GO

diagram

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1381 More actions · Answer 1

PLAN & COUNT are reserved keywords. If you're going to use them as an object (e.g., column) name or alias, you need to wrap them in double quotes or square brackets

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

SELECT 
  p.name AS [Plan], --<<--
--everything else same as before--

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

yrstruly Hall of Fame Points: 3475 More actions · Answer 3

Thank you. The column names were also a problem. I corrected it.

SELECT 
  p.name AS [Plan],
  COUNT(s.ID) AS [Count],
  SUM(t.amount) AS [Amount ($)]
FROM 
  Subscriptions s
JOIN 
  Plans p ON s.plan_ID = p.ID
JOIN 
  Transactions t ON s.ID = t.Subscription_ID
WHERE 
  s.Status = 'SUBSCRIBED'
GROUP BY 
  p.name
ORDER BY 
  p.name ASC;

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

Instead of quoting the column names - I would recommend changing the names to something else. For example:

plan_name instead of [Plan]

total_count instead of [Count]

total_amount_dollars instead of [Amount ($)]

Makes it much easier to code and any processes consuming this data doesn't have to worry about quoting column names on top of your code quoting the column names.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Jeffrey Williams wrote:

Instead of quoting the column names - I would recommend changing the names to something else. For example:
plan_name instead of [Plan]
total_count instead of [Count]
total_amount_dollars instead of [Amount ($)]
Makes it much easier to code and any processes consuming this data doesn't have to worry about quoting column names on top of your code quoting the column names.

Wise words. As much as possible, make your code clear, easy to read, and consistent. If you're going to use quotes or brackets, use them everywhere. But better, just do what's suggested here.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Y.B. SSChampion Points: 11649 More actions · Answer 6

Grant Fritchey wrote:

Jeffrey Williams wrote:
Instead of quoting the column names - I would recommend changing the names to something else. For example:
plan_name instead of [Plan]
total_count instead of [Count]
total_amount_dollars instead of [Amount ($)]
Makes it much easier to code and any processes consuming this data doesn't have to worry about quoting column names on top of your code quoting the column names.
Wise words. As much as possible, make your code clear, easy to read, and consistent. If you're going to use quotes or brackets, use them everywhere. But better, just do what's suggested here.

I agree 100% but sometimes you are coding for databases you didn't create. I use SQL Prompt but I believe the built-in Intellisense will also change the color of any reserved words which will stick out like a sore thumb. It's always good to train your brain to quote those immediately as you see them.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 7

Y.B. wrote:

I agree 100% but sometimes you are coding for databases you didn't create. I use SQL Prompt but I believe the built-in Intellisense will also change the color of any reserved words which will stick out like a sore thumb. It's always good to train your brain to quote those immediately as you see them.

When you are creating a query - you have control over the column alias names. If the database has columns that need to be quoted - then of course you need to do that, but at the same time you also have the ability to create a column alias that doesn't need to be quoted.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs