Understanding how WHERE works with GROUP BY and Aggregation

My query -

select cu.CustomerID,cu.FirstName,cu.LastName, COUNT(si.InvoiceID)as inv --1
from Customer as cu inner join SalesInvoice as si --2
on cu.CustomerID = si.CustomerID -- 3
-- put the WHERE clause here ! --4   
group by cu.CustomerID,cu.FirstName,cu.LastName -- 5
where cu.FirstName = 'mark' -- 6

Output with correct code -

enter image description here

Error i get - Incorrect syntax near the keyword 'where'.

Can you tell me why I get this error ? I want to know why WHERE comes before GROUP BY and not after.

Score: 13
Tags: sql
Views: 41558
Date posted: 12/22/2012

T-SQL IF statement embedded in a sum() function

I'm attempting to convert a MySQL query to a T-SQL query and the IF statement that's enclosed within a SUM statement is tripping me up. Any suggestions?

SELECT
    CMTS_RQ.[Dated],
    CMTS_RQ.CMTS_Name,
    Count(CMTS_RQ.CMTS_Name) AS emat_count,
    Sum(if(CMTS_RQ.US_Pwr>=37 and CMTS_RQ.US_Pwr<=49)) AS us_pwr_good
FROM
    CMTS_RQ
GROUP BY
    CMTS_RQ.CMTS_Name,
    CMTS_RQ.[Dated]

But I get an error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.

Score: 10
Tags: sql-server, tsql
Views: 25182
Date posted: 5/13/2013

Pivot table strings grouping under pivot column?
JOB        ENAME
--------  ----------
ANALYST    SCOTT
ANALYST    FORD
CLERK      SMITH
CLERK      ADAMS
CLERK      MILLER
CLERK      JAMES
MANAGER    JONES
MANAGER    CLARK
MANAGER    BLAKE
PRESIDENT  KING
SALESMAN   ALLEN
SALESMAN   MARTIN
SALESMAN   TURNER
SALESMAN   WARD

I would like to format the result set such that each job gets its own column:

CLERKS  ANALYSTS  MGRS   PREZ  SALES
------  --------  -----  ----  ------
MILLER  FORD      CLARK  KING  TURNER
JAMES   SCOTT     BLAKE        MARTIN
ADAMS             JONES        WARD
SMITH 

I tried

SELECT ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN from
(
  SELECT ename, job from emp
) as st
pivot
(
  SELECT ename
  FOR job in (ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN)
) as pivottable

I'm getting these errors

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'in'.

How to use pivot to group strings under pivot column?

Score: 10
Tags: sql, sql-server, sql-server-2008, pivot, pivot-table
Views: 20850
Date posted: 3/11/2013

How to Dynamically Create Views?

Background

I have a website that displays data unique to a client. The site required views to be created ever time a new client is added. Each client is unique and has a different identifying information unique to them. For example an ID number and a prefix.

Everytime a new client is added a new set of views is manually created using a standard view set, which is just changed each time to reflect the clients unique information. This is usually done using a Find and Replace in SQL Server Management Studio (SSMS)

What I have so far?

I have created a Winform app that captures the unique information and puts them into variables. These variables are then put into the the standard script that is used to create the views.

Problem

My script contains SMSS statements are not native SQL statements, this causes my program to error and break in its submission to the database.

The statement in question is the GOkey word used to run batches by SMSS.

What I have tried so far?

I have encapsulated the whole script using String Literal and have inserted a new line before and after the GO statements as suggested in another question. but it didn't seem to work.

What I am trying now?

Using REGEX to split the script up at every 'GO' occurrence. This isn't working either.

Question

Is there a better solution to this problem or a fix for my solution?

Code

 string connectionString = fmDbSelect();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    connection.Open();
                    var scripts = Regex.Split(sql, @"^\w+GO$", RegexOptions.Multiline);
                    foreach (var splitScript in scripts)
                    {
                        command.CommandText = splitScript;
                        command.ExecuteNonQuery();

                    }


                }
            }

Error Message

{"Incorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\n'CREATE VIEW' must be the first statement in a query batch.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'LIKE'.\r\nIncorrect syntax near 'ANSI_NULLS'."}

My Script

/****** Object: View [dbo].[TIDEreportEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEreportEmails] AS SELECT EmailID, EmailContent, EmailSubject, EmailTo, EmailFrom, UserID, ObjectValueID, EmailSent, EmailCreated, EmailRead, EmailFromName, EmailType, EmailFailed, CASE WHEN emailread IS NULL THEN 'Not Read' ELSE 'Read' END AS EmailStatus FROM DEReportingClient2DB.dbo.Emails AS Emails_1 WHERE (UserID IN (SELECT UserID FROM DEReportingClient2DB.dbo.Users WHERE (ClientID = 195)))

GO

/****** Object: View [dbo].[TIDEunreadEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEunreadEmails] AS SELECT COUNT(*) AS UnreadEmails, UserID FROM dbo.TIDEreportEmails WHERE
(EmailRead IS NULL) GROUP BY UserID

Score: 5
Tags: c#, sql-server, regex, string
Views: 231
Date posted: 3/1/2015

Sum a union query

I'm using Microsoft SQL Svr Mgmt Studio 2008. I don't have access to create a temporary table (company restricts ability to create or modify tables) or I would use that to solve this problem.

I have successfully used a union query to combine the results of three select queries. Now I am trying to sum the results of the union.

When I execute the query below I receive:

Incorrect syntax near the keyword 'GROUP'

And then when I remove the group by I get:

Incorrect syntax near ')'

Here's my query so far:

Select Period, PCC, SUM(BasicHits), SUM(FareHits), SUM(SearchHits)
From (

SELECT     AAAPeriod AS Period, 
AAAFromPCC AS PCC, 
- SUM(AAABasic) AS BasicHits,
- SUM(AAAFare) AS FareHits,
- SUM(AAASearch) AS SearchHits
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10') 

UNION ALL

SELECT     AAAPeriod, 
AAAtoPCC,
SUM(AAABasic),
SUM(AAAFare),
SUM(AAASearch) 
FROM        HitsAaa
HAVING     (AAAPeriod = N'2010-10')

UNION ALL

SELECT      AgtPeriod, 
AgtPcc, 
SUM(AgtBasic),
SUM(AgtFare),
SUM(AgtSearch)
FROM          HitsAgent
HAVING      (AgtPeriod = N'2010-10')

)GROUP BY Period, PCC

I haven't been able to find a solution to this on any of the previous questions.

Score: 5
Tags: sql, sql-server, union
Views: 22893
Date posted: 4/10/2011


1


© 2021 Search Overflow
Contributions licensed under cc by-sa