group by problem - SQL Server Forums

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

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

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

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

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

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

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

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.

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

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?

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

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

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

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources