Group by, but not really..... - 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 2000 Forums

Transact-SQL (2000)

Group by, but not really.....

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-09-20 : 10:51:32

Hey all-
I have a table with job cost in it and most records have a part number in there, but some don't. When there is not a part number, I would like to insert Account number. This would be fine, but it causes the summary to break apart even more when I put account in the GROUP BY clause. Anyway around this?

SELECT
T1.ER_J_ID, T1.ER_DEPT,
--NEED TO RETURN ACCOUNT IF NO PART NUMBER
CASE WHEN T1.ER_P_ID = '' THEN T1.ER_ACCOUNT ELSE T1.ER_P_ID END AS "PART_NUMBER",
CASE WHEN T3.P_DESC = '' OR T3.P_DESC IS NULL THEN 'ACCOUNT DESC' ELSE T3.P_DESC END AS "DESCRIPTION"
FROM E_MASTER AS T1
--JOIN PART COST FOR ETC
LEFT JOIN E_PARTJOB_COST AS T2 ON T1.ER_P_ID = T2.PJ_P_ID
AND YEAR(T1.EO_YEARMONTH) = YEAR(T2.PJ_YEAR)
AND ER_J_ID = PJ_J_ID
--PART DESCRIPTION
LEFT JOIN E_PARTS AS T3 ON T1.ER_P_ID = T3.P_ID
WHERE T1.ER_J_ID = 'A0027'
AND T1.ER_ACCOUNT LIKE '501%'
GROUP BY
T1.ER_J_ID, T1.ER_DEPT, T1.ER_P_ID, T3.P_DESC
ORDER BY
T1.ER_J_ID, T1.ER_DEPT, "PART_NUMBER", "DESCRIPTION"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 11:44:45

well you could do
select *
from (your select here without group by)
group by FieldsYouWantToGroupOn

Go with the flow & have fun! Else fight the flow

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-09-20 : 11:48:18

You can use the same CASE statement in the group by clause too.
GROUP BY
T1.ER_J_ID, T1.ER_DEPT, CASE WHEN T1.ER_P_ID = '' THEN T1.ER_ACCOUNT ELSE T1.ER_P_ID END AS "PART_NUMBER", CASE WHEN T3.P_DESC = '' OR T3.P_DESC IS NULL THEN 'ACCOUNT DESC' ELSE T3.P_DESC END AS "DESCRIPTION"

- Sekar

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-09-20 : 12:05:01

Great! I totally forgot about that. Some days I feel like I have blinders on!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources