sql server Group by with calculated values

Question

I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated variables in group by statement (in group by should stay only name of the variable)? Please find me the solution wrom which total sql code is as small as possible.

select
 p.id,
 p.name,
 case when
 (p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 end as validity_name,
 p.surname,
 case when
 (p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%')) then 0 else 1 end as validity_surname,
 p.adress,
 sum(pr.value) as value
from [dbo].[_data_CRM_COSTOMER] as p inner join
 [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
group by id, name, validity_name, surname, validity_surname, adress

• AakashM: "total sql code is as small as possible" - why does the size of the code matter?!

Answer 1

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO
SELECT
 FirstName,
 ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
 p.id,
 p.name,
 p.surname,
 p.adress,
 a.validity_name,
 b.validity_surname,
 sum(pr.value) as value
from [dbo].[_data_CRM_COSTOMER] as p inner join
 [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
 CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
 CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress

• Anja: Thanks Mark very much! Cross apply works. Please help me with validity_name and validity_surname variables.
• Anja: Before import costumer database for direct marketing activities (sending direct mailing) I want to check (control) if names and surnames are ok, because somethimes agents in call center istead of insering name (in name column) - insert "XXX" or "XXXXXXX" or "????" (instead of for example "Kim"). How can i "hunt" those names and surnames? So that I lates in code can say where validity_name not equal 1.
• Mark Sinkinson: @Anja One way of fixing what you have it to replace the OR between each NOT LIKE clause with AND.
• MickyT: You could also change your CASE statements to something like CAST(CHARINDEX('!!!',p.name,1) + CHARINDEX('XXX',p.name,1) + CHARINDEX('???',p.name,1) AS BIT)