Problem with grouping
-
I have the table with this structure and some date on it and I want to group the data:
table structure:
IDK
LiderID
LiderSalary
Assist1
Assist1Salary
Assist2
Assist2Salary
IDK LiderID LiderSalary Assist1 Assist1Salary Assist2 Assist2Salary
1 A 300 B 100 C 50
2 C 250 A 300 B 70
as you can se the LiderID can be leader or assistant and know I want to group like this
A Sum(Salary)
B Sum(Salary)
C Sum(Salary)
even if the A or B or C is as leader or assistant must sum LiderSalary or Assist1Salary or Assist2Salary
thnx in advanced if you post the solution for me!
-
The best solution would be to nomalize your data. So, I just used a CTE to temporarily normalize it for the query.
[font="Courier New"]CREATE TABLE #tmpPoorlyDesignedTable
(IDK INT, LiderID CHAR(1), LiderSalary MONEY, Assist1 CHAR(1), Assist1Salary MONEY, Assist2 CHAR(1), Assist2Salary MONEY)
INSERT #tmpPoorlyDesignedTable VALUES (1,'A',300,'B',100,'C',50)
INSERT #tmpPoorlyDesignedTable VALUES (2,'C',250,'A',300,'B',70)
; WITH Data (DataType, Name, Salary)
AS (
SELECT 'Lider', LiderID, LiderSalary FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary FROM #tmpPoorlyDesignedTable
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
GROUP BY
Name[/font]
-
Michael Earl
Thnx very much ti works perfect ...so thnx again!
just a little question if it any possiblity to implement this in MS Access, I found the solution to link it with SQL Server but just to know the possibility to write the SQL code ( Query ) to view the result like this code!
Thnx again!
Dugi
-
Hi there...yesterday I was so tired so today I found the solution also for the MS Access comparing the code above...so everything is ok and now works perfect!
-
But little problem is coming...I have the date and when I add it the records going to calculate the null values during the Union data...how can I add the Date because I need it for the calculating in different period for example everything what happened between 01/01/2008 and 31/01/2008!
Michael Earl if you have this little solution for me. I will much appreciate !!!!
Thnx!
Dugi
-
I don't understand your question.
Perhaps you could post some sample data and an example of what you want.
-
hello again,
the code is the same:
IDK K_DATE LiderID LiderSalary Assist1 Assist1Salary Assist2 Assist2Salary
1 01/01/2008 A 300 B 100 C 50
2 02/01/2008 C 250 A 300 B 70
3 02/01/2008 B 100 A 50 C 200
the code above works find but with date I have problem, the records are going to multiplied you code is correct and I receive 53 rec, when I add the date it retrieves me 100 rec (I cannot understand why ..anyway) I want to put the date for the search criteria and seems like this:
WITH Data (DataType, Name, Salary)
AS (
SELECT 'Lider', LiderID, LiderSalary FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary FROM #tmpPoorlyDesignedTable
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
GROUP BY
Name
The question is where to put the K_DATE!?
Am I clear!?
Dugi
1 000 000 000 000 000 000 thnx!
:w00t:
-
HI again anyone has any idea how to retrieve the correct results from the situation that I posted above in my last post!
thnx !
:crying:
-
I think there are two possible solutions.
1.
WITH Data (DataType, Name, Salary, K_DATE)
AS (
SELECT 'Lider', LiderID, LiderSalary, K_DATE FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary, K_DATE FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary, K_DATE FROM #tmpPoorlyDesignedTable
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
GROUP BY
Name
or 2.
WITH Data (DataType, Name, Salary)
AS (
SELECT 'Lider', LiderID, LiderSalary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
GROUP BY
Name
-
michael.bertelsmeier (9/12/2008)
I think there are two possible solutions.1.
WITH Data (DataType, Name, Salary, K_DATE)
AS (
SELECT 'Lider', LiderID, LiderSalary, K_DATE FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary, K_DATE FROM #tmpPoorlyDesignedTable
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary, K_DATE FROM #tmpPoorlyDesignedTable
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
GROUP BY
Name
or 2.
WITH Data (DataType, Name, Salary)
AS (
SELECT 'Lider', LiderID, LiderSalary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
UNION ALL
SELECT 'Assist1', Assist1, Assist1Salary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
UNION ALL
SELECT 'Assist2', Assist2, Assist2Salary FROM #tmpPoorlyDesignedTable
WHERE K_DATE BETWEEN '01/01/2008' AND '31/01/2008'
)
SELECT
Name
, SUM(Salary) AS SalarySum
FROM
Data
GROUP BY
Name
Thanks a lot that works perfect ...the first option works for me ...also I tryed it as same like you post here but with little mistake I add K_DATE on SELECT statement so I have the msg " Column 'DATA.DATAK' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply