I’m really not sure if this belongs here, but i figured MS SQL Server is a Software, so yeah, here goes.
I’ve been running into a similar Problem time and time again and have no way to solve this.
Currently i’m trying to aggregate some sales Data in Grafana. The Datasource is a MS SQL Server 2012. I’ll throw the whole query here and try to explain whats going on:
SELECT IIF (SUM(CLE.I_CLERKTYPE_P) IN ('1', '6', '12', '17'), ( SELECT TOP 1 emp1.S_NAME2 + ' ' + emp1.S_NAME1 AS VB FROM INVOICE_P AS offer1 LEFT JOIN CLERKINV_P AS clerk1 on offer1.I_INVOICE_P = clerk1.I_INVOICE_P JOIN EMPLOYEE_M AS emp1 on clerk1.I_EMPLOYEE_M = emp1.I_EMPLOYEE_M WHERE offer1.DT_DELETED IS NULL AND clerk1.DT_DELETED IS NULL AND clerk1.I_CLERKTYPE_P = '1' AND offer1.S_INVNO = INV.S_INVNO ) , 'Kein VB') AS VB ,FORMAT(INV.N_INCL, 'C') FROM [StepsProd].[sao].[INVOICE_P] AS INV LEFT JOIN CLERKINV_P AS CLE on INV.I_INVOICE_P = CLE.I_INVOICE_P JOIN EMPLOYEE_M AS emp on CLE.I_EMPLOYEE_M = emp.I_EMPLOYEE_M WHERE INV.DT_DELETED IS NULL AND CLE.DT_DELETED IS NULL GROUP BY VB
Ok, The basic idea is, i have one table with sales (INVOICE_P) and another with corresponding salesperson (CLERKINV_P). Problem is, some Sales don’t have a salesperson. Rather than ommiting those, I’m assigning those a Value of “Kein VB”. All of this works great. I can get a list of sales with the correct corresponding sales person.
No the tricky part. I want to Sum those up and Group by the sales person. I have no clue how to do this. Since it is determined with a Subquerry, i have no decent way of grouping by it. I though by using the
AS i could reference it later, but SQL is complaining
VB is undefined. I also tried storing this in a variable with
AS VB into @vb but this doesn’t work either. I’m not sure if this is a mysql thing or such.
Last option was repeating the whole subquery in the Group by statement, but this doesn’t work either as SQL complains about using a Aggregation in a GROUP BY clause.
I’m at a loss here. The last thing i can think of, is using
DECLARE to create a table variable and storing all of this in it. I’ve never done this, so no clue if this would be correct.
Any help on this is welcome. And a more general approach on how to
GROUP BY fields that where “created” in the
SELECT statement would be really helpful, as i have this requirement quite often.
(Please forgive my potentially messy Style of writing SQL Querys. I’m self thought and am basically just copying stuff from Stackoverflow until it works )