Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 group by problem

Author  Topic 

Kerryman
Starting Member

17 Posts

Posted - 2013-06-18 : 10:15:54
Hi, I’ve had some help before with this issue but additional columns placed in the table have altered the solution provided by visakh16. The following is the output from a single table, if you consider rows 1 and 2 it seems to me that the information could be expressed on one row without the nulls or zeros, likewise for rows 3 and 4 etc;

EmployeeID ¦Name¦Year_¦Week_¦Contract_¦Site_ ¦BH¦OT1¦OT2¦OT3¦PayType1_¦PT1code_¦PT1¦PayType2_¦PT2code_¦PT2
BIR1¦EMPLOYEE1¦2014¦9¦56235¦RESOURCE ROOM**¦10¦0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00
BIR1¦EMPLOYEE1¦2014¦9¦56235¦RESOURCE ROOM**¦0¦0¦0¦0¦Travel Expenses¦ET¦25.00¦Price Work Hours¦AW¦20.00
BIR1¦EMPLOYEE1¦2014¦9¦56236!LAGO INTERNALL***¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00
BIR1¦EMPLOYEE1¦2014¦9¦56236¦LAGO INTERNAL*** ¦0¦ 0¦0¦0¦Price Work Hours¦AW¦25.00¦NULL¦NULL¦0.00
BIR1¦EMPLOYEE1¦2014¦9¦56237¦HOOTON STREET** ¦10¦ 0¦2¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00
BIR1¦EMPLOYEE1¦2014¦9¦56237¦HOOTON STREET** ¦0¦ 0¦0¦0¦Telephone VAT¦TV¦25.00¦NC Travel Ex¦XET¦15.00
BIS2¦EMPLOYEE2¦2014¦9¦56241¦SVR REFURB OWN* ¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NUL¦L NULL¦0.00
BIS2¦EMPLOYEE2¦2014¦9¦56241¦SVR REFURB OWN* ¦0¦ 0¦0¦0¦Price Work Hours¦AW¦25.00¦Digs Paid¦DP¦ 20.00
BIS2¦EMPLOYEE2¦2014¦9¦56242¦265 ILKESTON ROAD ¦10¦ 0¦1¦0¦NULL¦NULL¦0.00¦NULL¦ NULL¦0.00
BIS2¦EMPLOYEE2¦2014¦9¦56242¦265 ILKESTON ROAD ¦0¦ 0¦0¦0¦Telephone VAT¦TV¦25.00¦Price Work Hours¦AW¦20.00


The following select statement originally excluded the PT1code_ and PT2code_ to achieve the one row outcome;

SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int))AS 'BH',
SUM(CAST(OT1 / 60 AS int))AS 'OT1',
SUM(CAST(OT2 / 60 AS int))AS 'OT2',
SUM(CAST(OT3 / 60 AS int))AS 'OT3',
MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2,
FROM #TimesheetXL
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_]


The advent of the PT1code_, PT2code_ columns have changed things however as I can no longer use the MAX function else the return is incorrect. Any idea how I might return to the preferred output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 10:21:32
follow the same pattern


SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int))AS 'BH',
SUM(CAST(OT1 / 60 AS int))AS 'OT1',
SUM(CAST(OT2 / 60 AS int))AS 'OT2',
SUM(CAST(OT3 / 60 AS int))AS 'OT3',
MAX([PayType1_]) AS [PayType1_],
MAX([PT1code_]) AS [PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
MAX([PT2code_]) AS [PT2code_],
SUM(PT2) AS PT2
FROM #TimesheetXL
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-18 : 10:43:03
Hi visakh16, thank you again for your response, I did try the same pattern but if an employee has multiple paytypes on one contract then MAX(PT1code_) only gives me the maximum value for the codes inseted into the table... If that is not clear let me know if you would like to see a sample of the output
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 14:16:28
ok...in that case how are you planning to show the multiple values when you make them into a single row per EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] combination? please show your expected output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-18 : 15:43:15
Hi visakh16, in a nutshell there are 3 relevant records in the table, the output with MAX on the PTcode colums is this;

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT3
BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 30 0 3 0 Underpayment UP 75 Digs Paid DP 60 Travel Expenses DP 45

Ideally, if its possible I'd like to see this;

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT3
BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15
BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15
BOO2 STEPHEN BOOTH 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 01:04:35
then wont your initial query give you the same output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 04:36:01
Hi visakh16, it gives me the following, the difference being that BH and OT integer values arn't summed;

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 10 0 1 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15
BOO2 EMPLOYEE2 2014 9 56235 RESOURCE ROOM 10 0 1 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15
BOO2 EMPLOYEE3 2014 9 56235 RESOURCE ROOM 10 0 1 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 04:38:54
do you mean this then?

SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',
SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',
SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',
SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',

MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2,
FROM #TimesheetXL
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 05:17:51
Not sure why but the following error is being generated?

Column '#TimesheetXL.BH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 05:22:27
Sorry it should be this

SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],BH,OT1,OT2,OT3,
MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',
SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',
SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',
SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM #TimesheetXL
)t
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 05:40:00
Hi visakh16, apologies, on my previous post the employees should all have been shown as 'EMPLOYEE1' not 1,2 and 3. The output from your last solution provides this:

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 NULL NULL 0 NULL NULL 0
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Purchase Expense EP 25 APP - Incent Bonus AI 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Underpayment UP 25 Digs Paid DP 20

The total charge for wkeek 9 to contract 56235 should be BH 30 and OT2 3. The other paytypes are correct. Ideally I'd like to express on 3 rows like this;

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2 PayType3_ PT3code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20 Travel Expenses AF 15
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20 Mileage Expenses AI 15
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20 Telephone Charges DP 15

If thats possible?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 05:53:11
[code]
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
CASE WHEN Seq=1 THEN BH ELSE 0 END AS BH,
CASE WHEN Seq=1 THEN OT1 ELSE 0 END AS OT1,
CASE WHEN Seq=1 THEN OT2 ELSE 0 END AS OT2,
CASE WHEN Seq=1 THEN OT3 ELSE 0 END AS OT3,
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,
MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',
SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',
SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',
SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM #TimesheetXL
WHERE [PT1code_] > ''
OR [PT2code_] > ''
)t
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3
)r
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 06:03:32
Hi visakh16, the output from your last solution provides this:

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Price Work Hours AW 25 Additional Formans Pay AF 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20

On three rows but the BH and OT sums are expressed as zero
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:13:21
what does this give you?

SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,
MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',
SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',
SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',
SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM #TimesheetXL
WHERE [PT1code_] > ''
OR [PT2code_] > ''
)t
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 06:25:15
The following;

EmployeeID Name Year_ Week_ Contract_ Site_ Seq BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 1 0 0 0 0 Price Work Hours AW 25 Additional Formans Pay AF 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 2 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 3 0 0 0 0 Underpayment UP 25 Digs Paid DP 20
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:34:23
what about this?


SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
CASE WHEN Seq=1 THEN BH ELSE 0 END AS BH,
CASE WHEN Seq=1 THEN OT1 ELSE 0 END AS OT1,
CASE WHEN Seq=1 THEN OT2 ELSE 0 END AS OT2,
CASE WHEN Seq=1 THEN OT3 ELSE 0 END AS OT3,
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
ROW_NUMBER() OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_] ORDER BY EmployeeID ) AS Seq,BH,OT1,OT2,OT3,
MAX([PayType1_]) AS [PayType1_],
[PT1code_],
SUM(PT1) AS PT1,
MAX([PayType2_]) AS [PayType2_],
[PT2code_],
SUM(PT2) AS PT2
FROM
(
SELECT EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],
SUM(CAST(BH / 60 AS int)) OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'BH',
SUM(CAST(OT1 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT1',
SUM(CAST(OT2 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT2',
SUM(CAST(OT3 / 60 AS int))OVER (PARTITION BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_]) AS 'OT3',
[PayType1_],
[PT1code_],
PT1,
[PayType2_],
[PT2code_],
PT2
FROM #TimesheetXL
)t
WHERE [PT1code_] > ''
OR [PT2code_] > ''
GROUP BY EmployeeID,Name,[Year_],[Week_],[Contract_],[Site_],[PT1code_],[PT2code_] ,BH,OT1,OT2,OT3
)r


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kerryman
Starting Member

17 Posts

Posted - 2013-06-19 : 06:38:51
Perfect, thanks for all your help!

EmployeeID Name Year_ Week_ Contract_ Site_ BH OT1 OT2 OT3 PayType1_ PT1code_ PT1 PayType2_ PT2code_ PT2
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 30 0 3 0 Price Work Hours AW 25 Additional Formans Pay AF 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Purchase Expense EP 25 APP - Incent Bonus AI 20
BOO2 EMPLOYEE1 2014 9 56235 RESOURCE ROOM 0 0 0 0 Underpayment UP 25 Digs Paid DP 20
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:48:16
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -