Cannot perform an aggregate function on an expression containing an aggregate or a subquery

• paul.howard.raines

  Old Hand

  Points: 331

  More actions

  June 17, 2015 at 10:57 pm

  #319168

  Greetings.

  I am trying to select total figures from my database table, using aggregate functions.

  The trouble is: one of the columns I need requires that I run a sub-query within the aggregate. Which SQL does not allow.

  Here is the error I am getting :

  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

  Here is the initial query :

  select

  method,

  sum(payment_id) as payment_id,

  sum(status) as status,

  sum(allowEmailContact) as allowEmailContact,

  sum(allowPhoneContact) as allowPhoneContact,

  sum(totalReservations) as totalReservations

  from

  (SELECT

  RES.method, count(*) as payment_id,

  '' as status, '' as complete_data,

  '' as allowEmailContact, '' as allowPhoneContact,

  '' as totalReservations

  FROM

  Customer CUS

  INNER JOIN

  Reservation RES ON CUS.id = RES.customerId

  WHERE

  (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15

  23:59')

  AND RES.payment_id IS NOT NULL

  AND scope_id = 1

  GROUP BY

  RES.method

  UNION ALL

  etc

  etc

  ) AS results

  GROUP BY method

  (I used : "etc, etc, etc" to replace a large part of the query; I assume there is no need to write the entire code, as it is very long. But, the gist is clear)

  This query worked just fine.

  However, I need an extra field -- a field for those customers whose data are "clean" --- meaning : trimmed, purged of garbage characters (like : */?"#%), etc.

  I have a query that does that. But, the problem is: how to insert this query into my already existing query, so I can create that extra column?

  This is the query I am using to "clean" customer data :

  select *

  from dbo.Customer

  where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and

  Len(LTRIM(RTRIM(streetAddress))) <> '' and

  (Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and

  phone <> '' and Len(LTRIM(RTRIM(email))) > 5 and

  Len(LTRIM(RTRIM(email))) like '@' and

  Len(LTRIM(RTRIM(firstName))) > 2 and Len(LTRIM(RTRIM(lastName))) > 2) and

  Len(LTRIM(RTRIM(firstName))) <> '-' and Len(LTRIM(RTRIM(lastName))) <> '-' and

  Len(LTRIM(RTRIM(firstName))) is not null and

  Len(LTRIM(RTRIM(lastName))) is not null

  etc, etc

  This query works fine on its own.

  But, how to INSERT it into the initial query, to create a separate field, where I can get the TOTAL of those customers who meet this "clean" criteria?

  I tried it like this :

  select

  method,

  sum(payment_id) as payment_id,

  sum(status) as status,

  SUM((select *

  from dbo.Customer

  where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and

  Len(LTRIM(RTRIM(streetAddress))) <> '' and

  (Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and

  postalCode <> '00000' and postalCode <> '' and

  Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> '' and

  Len(LTRIM(RTRIM(email))) > 5 and

  Len(LTRIM(RTRIM(email))) like '@' and

  Len(LTRIM(RTRIM(firstName))) > 2 and

  Len(LTRIM(RTRIM(lastName))) > 2) and

  Len(LTRIM(RTRIM(firstName))) <> '-' and

  Len(LTRIM(RTRIM(lastName))) <> '-' and

  Len(LTRIM(RTRIM(firstName))) is not null and

  Len(LTRIM(RTRIM(lastName))) is not null) ) as clean_data,

  sum(allowEmailContact) as allowEmailContact, sum(allowPhoneContact) as allowPhoneContact,

  sum(totalReservations) as totalReservations

  from

  (SELECT

  RES.method, count(*) as payment_id, '' as status,

  '' as complete_data, '' as allowEmailContact,

  '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15

  23:59')

  AND RES.payment_id is not null and scope_id = 1

  GROUP BY RES.method

  UNION ALL

  etc

  etc

  etc

  and it gave me that "aggregate" error.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 18, 2015 at 1:51 am

  #1806272

  Hi Paul, and welcome to ssc.

  Can you post the whole query please, it's generally easier to work with a whole query, there's less chance of something you haven't seen messing things up.

  I noticed one or two odd things about your "clean customer" test:

  LEN() returns a positive integer, or NULL if the parameter is NULL.

  Use a proper NULL check as appropriate.

  Len(LTRIM(RTRIM(streetAddress))) <> ''

  Len(LTRIM(RTRIM(streetAddress))) is not null

  Len(LTRIM(RTRIM(postalCode))) is not null

  Len(LTRIM(RTRIM(email))) like '@'

  Len(LTRIM(RTRIM(firstName))) <> '-'

  Len(LTRIM(RTRIM(lastName))) <> '-'

  Len(LTRIM(RTRIM(firstName))) is not null

  Len(LTRIM(RTRIM(lastName))) is not null

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• paul.howard.raines

  Old Hand

  Points: 331

  More actions

  June 18, 2015 at 2:16 am

  #1806281

  ChrisM@Work (6/18/2015)

  ---

  Hi Paul, and welcome to ssc.

  Can you post the whole query please, it's generally easier to work with a whole query, there's less chance of something you haven't seen messing things up.

  I noticed one or two odd things about your "clean customer" test:

  LEN() returns a positive integer, or NULL if the parameter is NULL.

  Use a proper NULL check as appropriate.

  Len(LTRIM(RTRIM(streetAddress))) <> ''

  Len(LTRIM(RTRIM(streetAddress))) is not null

  Len(LTRIM(RTRIM(postalCode))) is not null

  Len(LTRIM(RTRIM(email))) like '@'

  Len(LTRIM(RTRIM(firstName))) <> '-'

  Len(LTRIM(RTRIM(lastName))) <> '-'

  Len(LTRIM(RTRIM(firstName))) is not null

  Len(LTRIM(RTRIM(lastName))) is not null

  Hi,

  thanks for your reply.

  Basically, I need the total count for EACH of the following fields :

  -- payment_id,

  -- status,

  -- customers with clean data,

  -- customers with dirty data (not cleaned)

  -- customers who have email addresses (containing the @ symbol), AND allow email communication (boolean value 1 or 0),

  -- customers who have phone numbers, AND allow phone communication (boolean value 1 or 0)

  -- reservations;

  These seven TOTALS are :

  (a) based on the criteria that : SCOPE_ID = 1

  (b) based on the criteria that : STATUS <> 'TEMPORARY'

  (C) grouped by the field METHOD (which is a field containing only 3 possible string values)

  Here is my full script :

  select method, sum(payment_id) as payment_id, sum(status) as status,

  (select count(*) from dbo.Customer where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and Len(LTRIM(RTRIM(streetAddress))) <> '' and

  (Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and postalCode <> 'Turku' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and

  phone <> '' and

  Len(LTRIM(RTRIM(email))) > 5 and

  Len(LTRIM(RTRIM(email))) like '@' and

  Len(LTRIM(RTRIM(firstName))) > 2 and Len(LTRIM(RTRIM(lastName))) > 2) and

  Len(LTRIM(RTRIM(firstName))) <> '-' and Len(LTRIM(RTRIM(lastName))) <> '-' and

  Len(LTRIM(RTRIM(firstName))) is not null and Len(LTRIM(RTRIM(lastName))) is not null and

  firstName not like '%#%' and firstName not like '%?%' and firstName not like '%+%' and

  firstName not like '%/%' and firstName not like '%\%' and firstName not like '%&%' and

  firstName not like '%<%' and firstName not like '%>%' and firstName not like '%_%' and

  firstName not like '%--%' and firstName not like '%*%' and firstName not like '%=%' and

  firstName not like '%(%' and firstName not like '%)%' and firstName not like '%[%' and

  firstName not like '%]%' and firstName not like '%%%' and firstName not like '%!%' and

  firstName not like '%|%' and firstName not like '%~%' and firstName not like '%''%' and

  firstName not like '%{%' and firstName not like '%}%' and firstName not like '%1%' and

  firstName not like '%2%' and firstName not like '%3%' and firstName not like '%4%' and

  firstName not like '%5%' and firstName not like '%6%' and firstName not like '%7%' and

  firstName not like '%8%' and firstName not like '%9%' and firstName not like '%0%' and

  lastName not like '%#%' and lastName not like '%?%' and lastName not like '%+%' and

  lastName not like '%/%' and lastName not like '%\%' and lastName not like '%&%' and

  lastName not like '%<%' and lastName not like '%>%' and lastName not like '%_%' and

  lastName not like '%--%' and lastName not like '%*%' and lastName not like '%=%' and

  lastName not like '%(%' and lastName not like '%)%' and lastName not like '%[%' and

  lastName not like '%]%' and lastName not like '%%%' and lastName not like '%!%' and

  lastName not like '%|%' and lastName not like '%~%' and lastName not like '%''%' and

  lastName not like '%{%' and lastName not like '%}%' and lastName not like '%1%' and

  lastName not like '%2%' and lastName not like '%3%' and lastName not like '%4%' and

  lastName not like '%5%' and lastName not like '%6%' and lastName not like '%7%' and

  lastName not like '%8%' and lastName not like '%9%' and lastName not like '%0%') as complete_data,

  (select count(*) from dbo.Customer where

  len(streetAddress) > 5 and streetAddress <> '' and streetAddress is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and

  Len(LTRIM(RTRIM(firstName))) > 2 and Len(LTRIM(RTRIM(lastName))) > 2 and

  Len(LTRIM(RTRIM(firstName))) <> '-' and Len(LTRIM(RTRIM(lastName))) <> '-' and

  Len(LTRIM(RTRIM(firstName))) is not null and Len(LTRIM(RTRIM(lastName))) is not null and

  firstName not like '%#%' and firstName not like '%?%' and firstName not like '%+%' and

  firstName not like '%/%' and firstName not like '%\%' and firstName not like '%&%' and

  firstName not like '%<%' and firstName not like '%>%' and firstName not like '%_%' and

  firstName not like '%--%' and firstName not like '%*%' and firstName not like '%=%' and

  firstName not like '%(%' and firstName not like '%)%' and firstName not like '%[%' and

  firstName not like '%]%' and firstName not like '%%%' and firstName not like '%!%' and

  firstName not like '%|%' and firstName not like '%~%' and firstName not like '%''%' and

  firstName not like '%{%' and firstName not like '%}%' and firstName not like '%1%' and

  firstName not like '%2%' and firstName not like '%3%' and firstName not like '%4%' and

  firstName not like '%5%' and firstName not like '%6%' and firstName not like '%7%' and

  firstName not like '%8%' and firstName not like '%9%' and firstName not like '%0%' and

  lastName not like '%#%' and lastName not like '%?%' and lastName not like '%+%' and

  lastName not like '%/%' and lastName not like '%\%' and lastName not like '%&%' and

  lastName not like '%<%' and lastName not like '%>%' and lastName not like '%_%' and

  lastName not like '%--%' and lastName not like '%*%' and lastName not like '%=%' and

  lastName not like '%(%' and lastName not like '%)%' and lastName not like '%[%' and

  lastName not like '%]%' and lastName not like '%%%' and lastName not like '%!%' and

  lastName not like '%|%' and lastName not like '%~%' and lastName not like '%''%' and

  lastName not like '%{%' and lastName not like '%}%' and lastName not like '%1%' and

  lastName not like '%2%' and lastName not like '%3%' and lastName not like '%4%' and

  lastName not like '%5%' and lastName not like '%6%' and lastName not like '%7%' and

  lastName not like '%8%' and lastName not like '%9%' and lastName not like '%0%') as almost_complete_data,

  sum(allowEmailContact) as allowEmailContact, sum(allowPhoneContact) as allowPhoneContact, sum(totalReservations) as totalReservations from

  (

  SELECT RES.method, count(*) as payment_id, '' as status, '' as complete_data, '' as almost_complete_data, '' as allowEmailContact, '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  AND RES.payment_id is not null and scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, count(*) as status, '' as complete_data, '' as almost_complete_data, '' as allowEmailContact, '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  AND RES.status = 'CANCELLED' and RES.status <> 'TEMPORARY' and scope_id = 1

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, count(*) as complete_data, '' as almost_complete_data, '' as allowEmailContact, '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  and scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as complete_data, count(*) as almost_complete_data, '' as allowEmailContact, '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  and scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as complete_data, '' as almost_complete_data, count(*) as allowEmailContact, '' as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  AND CUS.allowEmailContact = 1 and CUS.email like '%@%' and scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as complete_data, '' as almost_complete_data, '' as allowEmailContact, count(*) as allowPhoneContact, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59')

  AND CUS.allowPhoneContact = 1 and len(CUS.phone) > 6 and scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as complete_data, '' as almost_complete_data, '' as allowEmailContact, '' as allowPhoneContact, count(RES.id) as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE (RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59') and scope_id = 1

  and RES.status <> 'TEMPORARY'

  group by RES.method

  ) as results

  group by method

  And, before you ask : the reason I did all that crap with FIRSTNAME and LASTNAME is because : I couldn't come up with any other way to PURGE garbage data from the first and last names (stuff like : . *^?`"!#%&/)

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738337

  More actions

  June 18, 2015 at 2:42 am

  #1806288

  You can't do

  select sum( select myfield from mytable)

  from mytable

  If you want to work with two sets of data from the table and calculate different aggregates, then you need a second source.

  I would use a few CTEs here. Use one for each set of criteria you need. If you can clean things there, do that and then calculate the aggregates from the CTE then. Here's a partial example. you can use more CTEs if you need them

  WITH cteCleanCount ( total )

  AS ( SELECT COUNT(*) ,

  customerId

  FROM dbo.Customer

  WHERE LEN(LTRIM(RTRIM(streetAddress))) > 5

  AND LEN(LTRIM(RTRIM(streetAddress))) <> ''

  AND ( LEN(LTRIM(RTRIM(streetAddress))) IS NOT NULL

  AND LEN(LTRIM(RTRIM(postalCode))) = 5

  AND postalCode <> '00000'

  AND postalCode <> 'Turku'

  AND postalCode <> ''

  AND LEN(LTRIM(RTRIM(postalCode))) IS NOT NULL

  AND LEN(LTRIM(RTRIM(postalOffice))) > 2

  AND phone <> ''

  AND LEN(LTRIM(RTRIM(email))) > 5

  AND LEN(LTRIM(RTRIM(email))) LIKE '@'

  AND LEN(LTRIM(RTRIM(firstName))) > 2

  AND LEN(LTRIM(RTRIM(lastName))) > 2

  )

  AND LEN(LTRIM(RTRIM(firstName))) <> '-'

  AND LEN(LTRIM(RTRIM(lastName))) <> '-'

  AND LEN(LTRIM(RTRIM(firstName))) IS NOT NULL

  AND LEN(LTRIM(RTRIM(lastName))) IS NOT NULL

  AND firstName NOT LIKE '%#%'

  AND firstName NOT LIKE '%?%'

  AND firstName NOT LIKE '%+%'

  AND firstName NOT LIKE '%/%'

  AND firstName NOT LIKE '%\%'

  AND firstName NOT LIKE '%&%'

  AND firstName NOT LIKE '%<%'

  AND firstName NOT LIKE '%>%'

  AND firstName NOT LIKE '%_%'

  AND firstName NOT LIKE '%--%'

  AND firstName NOT LIKE '%*%'

  AND firstName NOT LIKE '%=%'

  AND firstName NOT LIKE '%(%'

  AND firstName NOT LIKE '%)%'

  AND firstName NOT LIKE '%[%'

  AND firstName NOT LIKE '%]%'

  AND firstName NOT LIKE '%%%'

  AND firstName NOT LIKE '%!%'

  AND firstName NOT LIKE '%|%'

  AND firstName NOT LIKE '%~%'

  AND firstName NOT LIKE '%''%'

  AND firstName NOT LIKE '%{%'

  AND firstName NOT LIKE '%}%'

  AND firstName NOT LIKE '%1%'

  AND firstName NOT LIKE '%2%'

  AND firstName NOT LIKE '%3%'

  AND firstName NOT LIKE '%4%'

  AND firstName NOT LIKE '%5%'

  AND firstName NOT LIKE '%6%'

  AND firstName NOT LIKE '%7%'

  AND firstName NOT LIKE '%8%'

  AND firstName NOT LIKE '%9%'

  AND firstName NOT LIKE '%0%'

  AND lastName NOT LIKE '%#%'

  AND lastName NOT LIKE '%?%'

  AND lastName NOT LIKE '%+%'

  AND lastName NOT LIKE '%/%'

  AND lastName NOT LIKE '%\%'

  AND lastName NOT LIKE '%&%'

  AND lastName NOT LIKE '%<%'

  AND lastName NOT LIKE '%>%'

  AND lastName NOT LIKE '%_%'

  AND lastName NOT LIKE '%--%'

  AND lastName NOT LIKE '%*%'

  AND lastName NOT LIKE '%=%'

  AND lastName NOT LIKE '%(%'

  AND lastName NOT LIKE '%)%'

  AND lastName NOT LIKE '%[%'

  AND lastName NOT LIKE '%]%'

  AND lastName NOT LIKE '%%%'

  AND lastName NOT LIKE '%!%'

  AND lastName NOT LIKE '%|%'

  AND lastName NOT LIKE '%~%'

  AND lastName NOT LIKE '%''%'

  AND lastName NOT LIKE '%{%'

  AND lastName NOT LIKE '%}%'

  AND lastName NOT LIKE '%1%'

  AND lastName NOT LIKE '%2%'

  AND lastName NOT LIKE '%3%'

  AND lastName NOT LIKE '%4%'

  AND lastName NOT LIKE '%5%'

  AND lastName NOT LIKE '%6%'

  AND lastName NOT LIKE '%7%'

  AND lastName NOT LIKE '%8%'

  AND lastName NOT LIKE '%9%'

  AND lastName NOT LIKE '%0%'

  GROUP BY Customer.CustomerId

  )

  SELECT method ,

  SUM(payment_id) AS payment_id ,

  SUM(status) AS status ,

  cteCleanCount.total

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  INNER JOIN cteCleanCount ON cteCleanCount.customerId = CUS.customerId

  WHERE ( RES.created > '2015-05-31 23:59'

  AND RES.created <= '2015-06-15 23:59'

  )

  AND RES.payment_id IS NOT NULL

  AND scope_id = 1

  AND RES.status <> 'TEMPORARY'

  GROUP BY RES.method;

  You can read about CTEs here: http://www.sqlservercentral.com/articles/122606/

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 18, 2015 at 3:38 am

  #1806298

  You will find this much easier to work with if you can reduce the clutter, and one way of doing this is to encapsulate sections of the code into other objects. String cleansing stands out as a fine example. Here's a little test harness to show you how it could easily be done:

  -----------------------------------------------------------------

  -- Dodgy-character detector

  -----------------------------------------------------------------

  DECLARE @Firstname VARCHAR(255)

  SET @Firstname = 'John#'+replicate('a',249)+'@'

  --SET @Firstname = 'Johnathan'

  -- Prototype

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  -- Change it to return a scalar value, clean or dirty

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  -- Configure as an inline table-valued function:

  -- =============================================

  -- Author:Chris Morris

  -- Create date: 20150618

  -- Description:returns 1 if the string contains anything other than a-z

  -- The function stops scanning through the string if it finds a match.

  -- =============================================

  CREATE FUNCTION [dbo].[iTVF_IsStringClean]

  (

  @String AS VARCHAR(255)

  )

  RETURNS TABLE

  AS

  RETURN

  (

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@String))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@String,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  )

  GO

  -- Test the iTVF

  SELECT d.Firstname, i.Cleaned

  FROM (VALUES

  ('John#'+replicate('a',249)+'@'),

  ('Johnathan')

  ) d (Firstname)

  CROSS APPLY [dbo].[iTVF_IsStringClean] (d.Firstname) i

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• paul.howard.raines

  Old Hand

  Points: 331

  More actions

  June 18, 2015 at 3:44 am

  #1806299

  ChrisM@Work (6/18/2015)

  ---

  You will find this much easier to work with if you can reduce the clutter, and one way of doing this is to encapsulate sections of the code into other objects. String cleansing stands out as a fine example. Here's a little test harness to show you how it could easily be done:

  -----------------------------------------------------------------

  -- Dodgy-character detector

  -----------------------------------------------------------------

  DECLARE @Firstname VARCHAR(255)

  SET @Firstname = 'John#'+replicate('a',249)+'@'

  --SET @Firstname = 'Johnathan'

  -- Prototype

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  -- Change it to return a scalar value, clean or dirty

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  -- Configure as an inline table-valued function:

  -- =============================================

  -- Author:Chris Morris

  -- Create date: 20150618

  -- Description:returns 1 if the string contains anything other than a-z

  -- The function stops scanning through the string if it finds a match.

  -- =============================================

  CREATE FUNCTION [dbo].[iTVF_IsStringClean]

  (

  @String AS VARCHAR(255)

  )

  RETURNS TABLE

  AS

  RETURN

  (

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@String))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@String,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  )

  GO

  -- Test the iTVF

  SELECT d.Firstname, i.Cleaned

  FROM (VALUES

  ('John#'+replicate('a',249)+'@'),

  ('Johnathan')

  ) d (Firstname)

  CROSS APPLY [dbo].[iTVF_IsStringClean] (d.Firstname) i

  Thanks Chris

  I will sort out the "cleaning" later

  For now, I just need to get the "total count" working.

  The code I am using is producing zeros for both the "clean" data and "unclean" data

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 18, 2015 at 4:05 am

  #1806304

  Does this simplification work for you? We can add Clean and Unclean next.

  SELECT

  RES.method,

  payment_id = SUM(CASE WHEN Filter = 1 THEN 1 ELSE 0 END),

  [status] = SUM(CASE WHEN Filter = 2 THEN 1 ELSE 0 END),

  [complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [almost_complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [allowEmailContact] = SUM(CASE WHEN Filter = 4 THEN 1 ELSE 0 END),

  [allowPhoneContact] = SUM(CASE WHEN Filter = 5 THEN 1 ELSE 0 END),

  [totalReservations] = SUM(CASE WHEN Filter = 6 THEN 1 ELSE 0 END)

  FROM Customer CUS

  INNER JOIN Reservation RES

  ON CUS.id = RES.customerId

  CROSS APPLY (

  SELECT Filter = CASE

  WHEN RES.payment_id is not null and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 1

  WHEN RES.status = 'CANCELLED' and RES.status <> 'TEMPORARY' and scope_id = 1 THEN 2

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 3

  WHEN CUS.allowEmailContact = 1 and CUS.email like '%@%' and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 4

  WHEN CUS.allowPhoneContact = 1 and len(CUS.phone) > 6 and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 5

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 6

  ELSE NULL END

  ) x

  WHERE RES.created >= '2015-06-01 00:00' AND RES.created < '2015-06-16 00:00'

  GROUP BY RES.method

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• paul.howard.raines

  Old Hand

  Points: 331

  More actions

  June 18, 2015 at 11:13 am

  #1806477

  ChrisM@Work (6/18/2015)

  ---

  Does this simplification work for you? We can add Clean and Unclean next.

  SELECT

  RES.method,

  payment_id = SUM(CASE WHEN Filter = 1 THEN 1 ELSE 0 END),

  [status] = SUM(CASE WHEN Filter = 2 THEN 1 ELSE 0 END),

  [complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [almost_complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [allowEmailContact] = SUM(CASE WHEN Filter = 4 THEN 1 ELSE 0 END),

  [allowPhoneContact] = SUM(CASE WHEN Filter = 5 THEN 1 ELSE 0 END),

  [totalReservations] = SUM(CASE WHEN Filter = 6 THEN 1 ELSE 0 END)

  FROM Customer CUS

  INNER JOIN Reservation RES

  ON CUS.id = RES.customerId

  CROSS APPLY (

  SELECT Filter = CASE

  WHEN RES.payment_id is not null and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 1

  WHEN RES.status = 'CANCELLED' and RES.status <> 'TEMPORARY' and scope_id = 1 THEN 2

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 3

  WHEN CUS.allowEmailContact = 1 and CUS.email like '%@%' and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 4

  WHEN CUS.allowPhoneContact = 1 and len(CUS.phone) > 6 and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 5

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 6

  ELSE NULL END

  ) x

  WHERE RES.created >= '2015-06-01 00:00' AND RES.created < '2015-06-16 00:00'

  GROUP BY RES.method

  Thanks again for your help, Chris

  This didn't work. But, I guess I should still keep searching...........

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  June 18, 2015 at 11:19 am

  #1806480

  ChrisM@Work (6/18/2015)

  ---

  You will find this much easier to work with if you can reduce the clutter, and one way of doing this is to encapsulate sections of the code into other objects. String cleansing stands out as a fine example. Here's a little test harness to show you how it could easily be done:

  -----------------------------------------------------------------

  -- Dodgy-character detector

  -----------------------------------------------------------------

  DECLARE @Firstname VARCHAR(255)

  SET @Firstname = 'John#'+replicate('a',249)+'@'

  --SET @Firstname = 'Johnathan'

  -- Prototype

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  -- Change it to return a scalar value, clean or dirty

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  -- Configure as an inline table-valued function:

  -- =============================================

  -- Author:Chris Morris

  -- Create date: 20150618

  -- Description:returns 1 if the string contains anything other than a-z

  -- The function stops scanning through the string if it finds a match.

  -- =============================================

  CREATE FUNCTION [dbo].[iTVF_IsStringClean]

  (

  @String AS VARCHAR(255)

  )

  RETURNS TABLE

  AS

  RETURN

  (

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@String))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@String,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  )

  GO

  -- Test the iTVF

  SELECT d.Firstname, i.Cleaned

  FROM (VALUES

  ('John#'+replicate('a',249)+'@'),

  ('Johnathan')

  ) d (Firstname)

  CROSS APPLY [dbo].[iTVF_IsStringClean] (d.Firstname) i

  Excellent Function Chris!

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  June 18, 2015 at 1:39 pm

  #1806506

  Please try this and see if it's right, or at least very close :-D. Btw, please add appropriate table aliases to all columns (scope_id, firstName (presumably CUS but not 100% sure), etc.).

  SELECT

  RES.method,

  SUM(CASE WHEN RES.payment_id is not null THEN 1 ELSE 0 END) AS payment_id,

  SUM(CASE WHEN RES.status = 'CANCELLED' THEN 1 ELSE 0 END) AS status,

  SUM(CASE WHEN aan1.is_streetAddress_Clean = 1 AND

  aan1.is_postalCode_Clean = 1 AND

  aan1.is_phone_Clean = 1 AND

  aan1.is_email_Clean = 1 AND

  aan1.are_firstName_lastName_Clean = 1

  THEN 1 ELSE 0 END) AS complete_data,

  SUM(CASE WHEN aan1.is_streetAddress_Clean = 1 AND

  aan1.is_postalCode_Clean = 1 AND

  aan1.are_firstName_lastName_Clean = 1

  THEN 1 ELSE 0 END) AS almost_complete_data,

  SUM(CASE WHEN CUS.allowEmailContact = 1 AND CUS.email LIKE '%@%' THEN 1 ELSE 0 END) AS allowEmailContact,

  SUM(CASE WHEN CUS.allowPhoneContact = 1 and len(CUS.phone) > 6 THEN 1 ELSE 0 END) AS allowPhoneContact,

  COUNT(RES.id) AS totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  CROSS APPLY (

  SELECT

  CASE WHEN (LEN(LTRIM(streetAddress)) > 5)

  THEN 1 ELSE 0 END AS is_streetAddress_Clean,

  CASE WHEN (LEN(LTRIM(postalCode)) = 5 AND postalCode NOT IN ('', '00000', 'Turku') AND LEN(LTRIM(postalOffice)) > 2)

  THEN 1 ELSE 0 END AS is_postalCode_Clean,

  CASE WHEN (phone > '')

  THEN 1 ELSE 0 END AS is_phone_Clean,

  CASE WHEN (LEN(LTRIM(email)) > 5 AND email LIKE '%@%')

  THEN 1 ELSE 0 END AS is_email_Clean,

  CASE WHEN (LEN(LTRIM(firstName)) > 1 AND firstName NOT LIKE '%[^A-Z -]%') AND

  (LEN(LTRIM(lastName)) > 2 AND lastName NOT LIKE '%[^A-Z -]%')

  THEN 1 ELSE 0 END AS are_firstName_lastName_Clean

  ) AS aan1 --assign_alias_names1

  WHERE RES.created > '2015-05-31 23:59'

  AND RES.created <= '2015-06-15 23:59'

  AND RES.status <> 'TEMPORARY'

  AND scope_id = 1

  GROUP BY RES.method

  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".

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 19, 2015 at 1:54 am

  #1806598

  paul.howard.raines (6/18/2015)

  ---

  ChrisM@Work (6/18/2015)

  ---

  Does this simplification work for you? We can add Clean and Unclean next.

  SELECT

  RES.method,

  payment_id = SUM(CASE WHEN Filter = 1 THEN 1 ELSE 0 END),

  [status] = SUM(CASE WHEN Filter = 2 THEN 1 ELSE 0 END),

  [complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [almost_complete_data] = SUM(CASE WHEN Filter = 3 THEN 1 ELSE 0 END),

  [allowEmailContact] = SUM(CASE WHEN Filter = 4 THEN 1 ELSE 0 END),

  [allowPhoneContact] = SUM(CASE WHEN Filter = 5 THEN 1 ELSE 0 END),

  [totalReservations] = SUM(CASE WHEN Filter = 6 THEN 1 ELSE 0 END)

  FROM Customer CUS

  INNER JOIN Reservation RES

  ON CUS.id = RES.customerId

  CROSS APPLY (

  SELECT Filter = CASE

  WHEN RES.payment_id is not null and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 1

  WHEN RES.status = 'CANCELLED' and RES.status <> 'TEMPORARY' and scope_id = 1 THEN 2

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 3

  WHEN CUS.allowEmailContact = 1 and CUS.email like '%@%' and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 4

  WHEN CUS.allowPhoneContact = 1 and len(CUS.phone) > 6 and scope_id = 1 and RES.status <> 'TEMPORARY' THEN 5

  WHEN scope_id = 1 and RES.status <> 'TEMPORARY' THEN 6

  ELSE NULL END

  ) x

  WHERE RES.created >= '2015-06-01 00:00' AND RES.created < '2015-06-16 00:00'

  GROUP BY RES.method

  Thanks again for your help, Chris

  This didn't work. But, I guess I should still keep searching...........

  It won't take much to make it work. It's a CROSSTAB query, a very standard way to go. It's efficient, often more so than the built-in PIVOT operator, and it's easy to tweak and augment so it's well worth investing a little time on it.

  "This didn't work" doesn't give folks much to go on. Did it throw an error? Return no results at all? Wrong results? Some correct, some wrong? Help us out here. Someone's run off with the crystal ball again :hehe:

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 19, 2015 at 2:06 am

  #1806600

  Alan.B (6/18/2015)

  ---

  ChrisM@Work (6/18/2015)

  ---

  You will find this much easier to work with if you can reduce the clutter, and one way of doing this is to encapsulate sections of the code into other objects. String cleansing stands out as a fine example. Here's a little test harness to show you how it could easily be done:

  -----------------------------------------------------------------

  -- Dodgy-character detector

  -----------------------------------------------------------------

  DECLARE @Firstname VARCHAR(255)

  SET @Firstname = 'John#'+replicate('a',249)+'@'

  --SET @Firstname = 'Johnathan'

  -- Prototype

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  -- Change it to return a scalar value, clean or dirty

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@Firstname))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@Firstname,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  -- Configure as an inline table-valued function:

  -- =============================================

  -- Author:Chris Morris

  -- Create date: 20150618

  -- Description:returns 1 if the string contains anything other than a-z

  -- The function stops scanning through the string if it finds a match.

  -- =============================================

  CREATE FUNCTION [dbo].[iTVF_IsStringClean]

  (

  @String AS VARCHAR(255)

  )

  RETURNS TABLE

  AS

  RETURN

  (

  SELECT CASE WHEN (

  SELECT TOP 1 n

  FROM (

  SELECT TOP(LEN(@String))

  n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

  FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) d (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) e (n),

  (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) f (n)

  ) iTally (n)

  WHERE SUBSTRING(@String,n,1) LIKE '[^A-Za-z]'

  ORDER BY n

  ) IS NOT NULL THEN 0 ELSE 1 END AS Cleaned

  )

  GO

  -- Test the iTVF

  SELECT d.Firstname, i.Cleaned

  FROM (VALUES

  ('John#'+replicate('a',249)+'@'),

  ('Johnathan')

  ) d (Firstname)

  CROSS APPLY [dbo].[iTVF_IsStringClean] (d.Firstname) i

  Excellent Function Chris!

  Thanks Alan, it was fun writing it

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• paul.howard.raines

  Old Hand

  Points: 331

  More actions

  June 22, 2015 at 12:48 am

  #1806936

  Thank you all for your very helpful replies.

  I broke down my query to remove that "firstname and lastname" nonsense.

  Now, my query is running based on criteria like : phone, email, advertizing.

  It executes without errors.

  Only problem : it's giving me the SAME total-counts for each criteria

  Here is the query script :

  select method, sum(payment_id) as payment_id, sum(status) as status,

  (select count(*) from dbo.Customer where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and Len(LTRIM(RTRIM(streetAddress))) <> '' and

  Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and postalCode <> 'Turku' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> ''

  and allowMarketing = 0

  ) as criteria_1,

  (select count(*) from dbo.Customer where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and Len(LTRIM(RTRIM(streetAddress))) <> '' and

  Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and postalCode <> 'Turku' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> ''

  and(allowPhoneContact = 1 and allowEmailContact = 0)

  ) as criteria_2,

  (select count(*) from dbo.Customer where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and Len(LTRIM(RTRIM(streetAddress))) <> '' and

  Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and postalCode <> 'Turku' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> ''

  and email like '%@%' and (allowPhoneContact = 0 and allowEmailContact = 0)

  ) as criteria_3,

  (select count(*) from dbo.Customer where

  Len(LTRIM(RTRIM(streetAddress))) > 5 and Len(LTRIM(RTRIM(streetAddress))) <> '' and

  Len(LTRIM(RTRIM(streetAddress))) is not null and

  Len(LTRIM(RTRIM(postalCode))) = 5 and postalCode <> '00000' and postalCode <> 'Turku' and

  postalCode <> '' and Len(LTRIM(RTRIM(postalCode))) is not null and

  Len(LTRIM(RTRIM(postalOffice))) > 2 and phone <> ''

  and email like '%@%' and (allowPhoneContact = 1 and allowEmailContact = 1)

  ) as criteria_4,

  sum(totalReservations) as totalReservations from

  (

  SELECT RES.method, count(*) as payment_id, '' as status, '' as criteria_1, '' as criteria_2, '' as criteria_3, '' as criteria_4, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, count(*) as status, '' as criteria_1, '' as criteria_2, '' as criteria_3, '' as criteria_4, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  AND RES.status = 'CANCELLED' and RES.status <> 'TEMPORARY' and RES.scope_id = 1

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, count(*) as criteria_1, '' as criteria_2, '' as criteria_3, '' as criteria_4, '' as totalReservations

  FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  and RES.scope_id = 1 and RES.status <> 'TEMPORARY'

  and CUS.allowMarketing = 0

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as criteria_1, count(*) as criteria_2, '' as criteria_3, '' as criteria_4, '' as totalReservations FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  and RES.scope_id = 1 and RES.status <> 'TEMPORARY'

  and CUS.allowPhoneContact = 1 and CUS.allowEmailContact = 0

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as criteria_1, '' as criteria_2, count(*) as criteria_3, '' as criteria_4, '' as totalReservations FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  and RES.scope_id = 1 and RES.status <> 'TEMPORARY'

  and CUS.email like '%@%' and CUS.allowPhoneContact = 0 and CUS.allowEmailContact = 0

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as criteria_1, '' as criteria_2, '' as criteria_3, count(*) as criteria_4, '' as totalReservations FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  and RES.scope_id = 1 and RES.status <> 'TEMPORARY'

  and CUS.email like '%@%' and CUS.allowPhoneContact = 1 and CUS.allowEmailContact = 1

  group by RES.method

  union all

  SELECT RES.method, '' as payment_id, '' as status, '' as criteria_1, '' as criteria_2, '' as criteria_3, '' as criteria_4, count(*) as totalReservations FROM Customer CUS

  INNER JOIN Reservation RES ON CUS.id = RES.customerId

  WHERE RES.created > '2015-05-31 23:59' and RES.created <= '2015-06-15 23:59'

  and RES.scope_id = 1 and RES.status <> 'TEMPORARY'

  group by RES.method

  ) as results

  group by method

  I have tried so many different ways to modify the query, but I keep getting the same totals

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 24, 2015 at 2:07 am

  #1807652

  paul.howard.raines (6/22/2015)

  ---

  Thank you all for your very helpful replies.

  I broke down my query to remove that "firstname and lastname" nonsense.

  Now, my query is running based on criteria like : phone, email, advertizing.

  It executes without errors.

  Only problem : it's giving me the SAME total-counts for each criteria

  Here is the query script :...snip...

  You've shown us two queries which don't work and we've shown you two (or more?) ways of getting what you want - but this most recent query doesn't take on board any of the suggestions made to you. What you are trying to do is trivial and commonplace to the folks here, but novel and difficult for you.

  What I suggest you do is revisit the previous posts and have a play with the code, explore the concepts, and the difference between the proposed solutions and your not-working queries. You might also benefit from reading this article[/url].

  If you're still stuck after this, then beginning with a couple of working aggregates, post up what you've got and folks will work with you. Note that this is a two-way street, and writing off other people's efforts with an unqualified "it doesn't work" is unlikely to endear you to the folks who are trying to help. Provide as much information as you can so folks can tweak their code to fit your requirement.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden