SELECT and GROUP BY Confusion

[JDTurk]

I'm pleading for anyone's help in getting me to understand how GROUP BY is used. The problem is I have items to report that I don't want to be part of the GROUP BY.

I need to GROUP BY Emp ID, Ded Code, and Pay Date in order to total by Emp ID & Ded Code. I also need to report Emp Name, Emp Dept, etc., but it means that I have to add these items to the GROUP BY. But when I do that it screws up my report. How can I add these additional items?

Thank you all.

S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[SkipVought]

hi,

I never [highlight #FCE94F]Group By Confusion[/highlight]! ;-)

However, on a more serious note, please post the SQL code in question, including the fields that are wreaking havoc
with your expected results.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JDTurk]

Hey Skip, I really appreciate your help.

SELECT d.empl_no, d.code,

CASE
WHEN b.ck_title = 'RETIRE' THEN 'RETIREMENT'
WHEN b.ck_title = 'B/C' THEN 'HEALTH INS'
WHEN b.ck_title = 'KEYSTONE' THEN 'HEALTH INS'
WHEN b.ck_title = 'POS' THEN 'HEALTH INS'
ELSE b.ck_title
END,

d.pay_date, d.amount,

CASE
WHEN GROUPING(d.code) = 0 and GROUPING(d.empl_no) = 0 and
GROUPING(d.pay_date) = 1 and GROUPING(d.amount) = 1 and
GROUPING(b.ck_title) = 1
THEN 'Sub Total' + cast(d.empl_no as varchar(8)) + '/' +
cast(d.code as varchar(4))
end,
sum(d.amount)

from detdist d

inner join employee e on e.empl_no = d.empl_no
inner join person p on p.empl_no = d.empl_no
inner join payrate r on r.empl_no = d.empl_no
inner join payroll y on y.empl_no = d.empl_no
Inner Join dist_orgn h on h.empl_no = d.empl_no
left join bartable l on l.code = p.bargain
left join employee_type m on m.code = p.empl_type
left join clstable c on c.class_cd = r.classify
left join dedtable b on b.ded_cd = d.code

where p.status='ACT' and SUBSTRING(h.orgn, 1, 3) IN ('058','114','128','213','300')
and ( (d.code between '0002' and '1005' ) or
(d.code between '1019' and '1025' ) or
(d.code between '3001' and '4399' ) or
(d.code between '5001' and '5098' ) or
(d.code between '5101' and '5102' ))
and d.code is not NULL
and (d.rec_type <> 'F' and d.rec_type <> 'R')
and (d.pay_date >= '2012/09/01' and d.pay_date <= '2013/08/31')

GROUP BY d.empl_no, d.code, b.ck_title, d.pay_date, d.amount WITH ROLLUP

But I also need to include this code:

-- select d.empl_no, RTRIM(e.l_name) 'Last', RTRIM(e.f_name) 'First',
-- CONVERT(Decimal(10,0),r.days_worked) 'Days Worked', RTRIM(d.orgn_proj) 'Orgn',
-- RTRIM(d.acct) 'Account', CONVERT(Decimal(4,2),h.prcent) '%',
-- CASE d.rec_type
-- WHEN 'F' THEN 'FRINGE BENS'
-- WHEN 'R' THEN 'FRINGE BENS (PROJECT)'
-- WHEN 'O' THEN 'GROSS WAGES'
-- WHEN 'P' THEN 'GROSS WAGES (PROJECT)'
-- WHEN 'D' THEN 'DEDUCTS/TAXES'
-- WHEN 'W' THEN 'WORKERS COMP'
-- END,


S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[SkipVought]

But I also need to include this code:

Include...[highlight #FCE94F]WHERE[/highlight]?

select
  d.empl_no
, RTRIM(e.l_name) 'Last'
, RTRIM(e.f_name) 'First'
, CONVERT(Decimal(10,0),r.days_worked) 'Days Worked'
, RTRIM(d.orgn_proj) 'Orgn'
, RTRIM(d.acct) 'Account'
, CONVERT(Decimal(4,2),h.prcent) '%'
, CASE d.rec_type
    WHEN 'F' THEN 'FRINGE BENS'
    WHEN 'R' THEN 'FRINGE BENS (PROJECT)'
    WHEN 'O' THEN 'GROSS WAGES'
    WHEN 'P' THEN 'GROSS WAGES (PROJECT)'
    WHEN 'D' THEN 'DEDUCTS/TAXES'
    WHEN 'W' THEN 'WORKERS COMP'
  END
,

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SQLBill]

SELECT and GROUP BY work like this: I want Col1, Col2, Col3....but I want you to GROUP it BY only Col1 and Col3. SQL Server doesn't know what you want done with Col2. How is that supposed to fit into the grouping? The GROUP BY also tells SQL Server what order the columns should be grouped by. So leaving out a column that is in the select confuses SQL Server since it can't tell how that column should be included (grouped) with the other columns.

One solution might be to use a derived table, with all the data that needs to be grouped and then join the rest of the columns you need.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[JDTurk]

Skip, I meant that I also need to report the items in that code.

Bill, thanks for your reply. It just doesn't make any sense to me that we are so limited when using GROUP BY. I mean, I have to have employee names on this report.

S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[gmmastros]

Usually, the trick here is to write a query that produces the grouping results you want and then to join that query with an outer query that also includes your other columns.

For example, suppose you had a query that summed the pay amounts for an employee. Something like this:

Select EmployeeId, 
       Sum(PayAmount) As TotalPay
From   EmployeePayment
Where  PayDate >= '2013-01-01'
       And PayDate < '2014-01-01'
Group By EmployeeId

The query shown above would return all of the pay that each employee received in 2013. The problem now is that we also want to return additional information about each employee. To do that:

Select  Employee.EmployeeId,
        Employee.Name,
        Employee.OtherColumns,
        [green]Payments.[/green]TotalPay
From    Employee
        Inner Join [!](
          Select EmployeeId, 
                 Sum(PayAmount) As TotalPay
          From   EmployeePayment
          Where  PayDate >= '2013-01-01'
                 And PayDate < '2014-01-01'
          Group By EmployeeId
          ) As Payments[/!]
          On Employee.EmployeeId = [green]Payments.[/green]EmployeeId

Note several things here. First, the original query is in there and surrounded by parenthesis. This makes the original query become a derived table. Note that the parenthesis are required and the alias (Payments) is also required. When referencing the data in the derived table, you must reference it by alias name like I did in the last select line and also in the 'on clause'.

The derived table part is valid SQL because it has a group by and all columns that are returned are included in the group by or included in an aggregate. Since you are grouping in a derived table, you don't need to worry about the columns returned in the outer query because there is no grouping in the outer query.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JDTurk]

Gmmastros,

Thanks for taking the time to assist me. I appreciate it.

S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[gmmastros]

Did it work? Do you understand it?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JDTurk]

I understood your logic completely but, as usual, the code didn't work for me. I was able to do the calculations in Excel which allowed me to remove any vestige of GROUP BY and my life in SQL World got better once again.

S8730 Processors (Redundncy/Mirror Img)
Aura 5.2 (Communication Manager)
Modular Messaging 5.2

 

[gmmastros]

Sounds like you found another way to solve the problem. Are you still interested in getting this to work purely in SQL? If so, what error (if any) did you get?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom