I just can't believe the Group by Function!

[tbailey922]

Hello,
I just can't believe the group by function in SQL Server. I am trying to grab some data from a table and group by only the first two columns, but in SQL you have to group by each column for the whole table. Which throws off the whole grouping. In foxpro you could select the whole table and group by whatever columns you wanted, is this possible in SQL? I have been stuck on the whole grouping thing for about a day now and am just about to give up.

Tim

 

[mrees]

tbailey922,

Could you give us an example of your source data and expected output? I am not sure what you are trying to achieve.

mrees

 

[MFritts253]

Try Order By instead of Group By

 

[ESquared]

GROUP BY works fine and logically. You have to either group by a column or have an aggregate function.

If you want other columns from your table, then make the GROUP BY query a derived table and join it to the other table. This will then repeat the rows from the derived table as necessary for the outer table.

Giving example data is a good idea.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[tbailey922]

Sorry for not giving an example. Here is part of my code.
"select job, subjob, dept, jobdesc,SUM(selling) AS complsalesprice,
SUM(actualcost + costtogo) AS complcost, SUM(actualcost) AS currentcost
from jcpmst
group by job, subjob, dept, jobdesc order by job, subjob"


I only want to group by job and subjob, if I group like it is stated above then I get a bunch of records with the same job and subjob number but different dept and jobdesc. It is useless that way.

Thanks
Tim

 

[redlam]

In Foxpro, if the dept and jobdesc were different for the same job and subjob, which dept and jobdesc values would you expect to see for this grouping? If you don't group by those values or apply an aggregate function to them, SQL does not know what to return. Try using max(dept) and max(jobdesc) or min(dept), min(jobdesc) if you do not want to group by these fields.

 

[tbailey922]

You make a vailid point Redlam it would grab the first record in the table and that would be the dept and jobdesc, I guess I am so used to it doing this. It is a big change for it not to do it.

Thanks
Tim

 

[ESquared]

ahhh... you left something out in your description, earlier. You want to throw away information, not GROUP BY it!

Anyway, for what it's worth, I, too, have wanted a First() or Last() aggregate function in SQL Server. The problem is that by definition rows are unordered in a relational database, so without an ORDER BY clause there cannot be a First() or Last().

In the meantime you can duplicate the functionality:

SELECT
   J.job,
   J.subjob,
   J.dept,
   J.jobdesc,
   complsalesprice = Sum(selling), 
   complcost       = Sum(actualcost + costtogo),
   currentcost     = Sum(actualcost)
FROM jcpmst J
   INNER JOIN (
      SELECT
         J1.job,
         J1.subjob,
         J1.dept,
         J1.jobdesc
      FROM jcpmst J1
         INNER JOIN jcpmst J2 ON
            J1.job = J2.job
            AND J1.subjob = J2.subjob
            AND (J1.dept > J2.dept
               OR (J1.dept = J2.dept AND J2.jobdesc >= J2.jobdesc)
            )
      GROUP BY
         J1.job,
         J1.subjob,
         J1.dept,
         J1.jobdesc
      HAVING Count(J2.job) = 1
   ) C ON J.job = C.job AND j.subjob = C.job AND J.dept = C.dept AND J.jobdesc = C.jobdesc
GROUP BY
   J.job,
   J.subjob,
   J.dept,
   J.jobdesc
ORDER BY
   job,
   subjob

This query can be simplified if dept + jobdesc were cast to character type and Min() used.

The gotcha is that the final Sum() results won't include the records whose dept and jobsdesc you don't see. I could write a query to give that, too.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[redlam]

I am the only SQL DBA in a house full of Foxpro'ers - GROUP BY was a big change for them as well. I believe they just made use of max() and min() whenever necessary.
Good luck!

 

[ESquared]

Good point, redlam, as long as you didn't mind divorcing dept from jobdesc. If you need them to be both from the same record, it complicates things by an order of magnitude.

-------------------------------------
A sacrifice is harder when no one knows you've made it.