Total of two - 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

SQL Server Development (2000)

Total of two

Author

Topic

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-04-05 : 10:35:44

Hello all,
I'm trying to get a total of COUNT 'Internal Repair' + COUNT 'External Repair' (Noted in red) Is this possible?

SELECT table_part_num.x_company, table_part_num.line AS [Part Type],
COUNT (CASE WHEN table_site.appl_type ='Internal Repair' AND (table_part_num.[domain] = 'Hardware Serialized') THEN table_site.appl_type ELSE NULL END) AS [Internal Repair],
COUNT (CASE WHEN table_site.appl_type ='External Repair' AND (table_part_num.[domain] = 'Hardware Serialized') THEN table_site.appl_type ELSE NULL END) AS [External Repair],

AVG (CASE WHEN table_site.appl_type ='Internal Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN
(DATEDIFF(hh,table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date)/ 24.0) ELSE NULL END) AS [Average Days Internal Repair],

AVG (CASE WHEN table_site.appl_type ='External Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN
(DATEDIFF(hh,table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date)/ 24.0) ELSE NULL END) AS [Average Days External Repair]

FROM table_part_num WITH (NOLOCK) INNER JOIN
table_x_part_move_audit WITH (NOLOCK) ON table_part_num.objid = table_x_part_move_audit.x_part_num_objid INNER JOIN
table_site ON table_x_part_move_audit.x_from_site_objid = table_site.objid
WHERE NOT table_x_part_move_audit.x_to_site_id LIKE '' AND
(table_part_num.[domain] = 'Hardware Serialized') AND (table_part_num.active = 'active') AND
table_site.appl_type IN ('Internal Repair','External Repair')
GROUP BY table_part_num.line, table_part_num.x_company, table_site.appl_type

Thanks in advacne for any help

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-05 : 10:42:15

[code]select x_company, [Part Type], [Internal Repair], [External Repair], ([Internal Repair] + [External Repair]) as Total, [Average Days Internal Repair], [Average Days External Repair]
From
(
SELECT
table_part_num.x_company,
table_part_num.line AS [Part Type],
COUNT (CASE WHEN table_site.appl_type ='Internal Repair' AND (table_part_num.[domain] = 'Hardware Serialized') THEN table_site.appl_type ELSE NULL END) AS [Internal Repair],
COUNT (CASE WHEN table_site.appl_type ='External Repair' AND (table_part_num.[domain] = 'Hardware Serialized') THEN table_site.appl_type ELSE NULL END) AS [External Repair],
AVG (CASE WHEN table_site.appl_type ='Internal Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN
(DATEDIFF(hh,table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date)/ 24.0) ELSE NULL END) AS [Average Days Internal Repair],
AVG (CASE WHEN table_site.appl_type ='External Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN
(DATEDIFF(hh,table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date)/ 24.0) ELSE NULL END) AS [Average Days External Repair]
FROM
table_part_num WITH (NOLOCK) INNER JOIN table_x_part_move_audit WITH (NOLOCK)
ON
table_part_num.objid = table_x_part_move_audit.x_part_num_objid INNER JOIN table_site
ON
table_x_part_move_audit.x_from_site_objid = table_site.objid
WHERE
table_x_part_move_audit.x_to_site_id <> '' AND
table_part_num.[domain] = 'Hardware Serialized' AND
table_part_num.active = 'active' AND
table_site.appl_type IN ('Internal Repair','External Repair')
GROUP BY
table_part_num.line, table_part_num.x_company, table_site.appl_type
) t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 10:44:01

Yes, but you will get an ANSI NULL warning. Use this instead.

SUM (CASE WHEN table_site.appl_type = 'Internal Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN 1 ELSE 0 END) AS [Internal Repair],
SUM (CASE WHEN table_site.appl_type = 'External Repair' AND table_part_num.[domain] = 'Hardware Serialized' THEN 1 ELSE 0 END) AS [External Repair],

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-05 : 10:49:01

Just add them up like

SELECT ...,
COUNT (CASE WHEN table_site.appl_type ='Internal Repair' AND (table_part_num.[domain] = 'Hardware Serialized') 
    THEN table_site.appl_type ELSE NULL END) +
COUNT (CASE WHEN table_site.appl_type ='External Repair' AND (table_part_num.[domain] = 'Hardware Serialized') 
    THEN table_site.appl_type ELSE NULL END) AS [Total Repairs],
...

Or you can make your query a subquery and add them up in the main query like

SELECT x_company, [Part Type], [Internal Repair], [External Repair],
    [Internal Repair] + [External Repair] AS [Total Repairs], 
    [Average Days Internal Repair], [Average Days External Repair]
FROM (your entire original query here) AS SQ

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-05 : 10:49:20

Oh! If the WHERE table_part_num.[domain] = 'Hardware Serialized' already exists, and you only have INNER JOINs,
you can simplify the COUNTs as this

SUM(CASE WHEN table_site.appl_type = 'Internal Repair' THEN 1 ELSE 0 END) AS [Internal Repair],
SUM(CASE WHEN table_site.appl_type = 'External Repair' THEN 1 ELSE 0 END) AS [External Repair],
AVG(CASE WHEN table_site.appl_type = 'Internal Repair' THEN DATEDIFF(hour, table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date) / 24.0 END) AS [Average Days Internal Repair],
AVG(CASE WHEN table_site.appl_type = 'External Repair' THEN DATEDIFF(hour, table_x_part_move_audit.x_start_date, table_x_part_move_audit.x_end_date) / 24.0 END) AS [Average Days External Repair]

Peter Larsson
Helsingborg, Sweden

callawayx14
Yak Posting Veteran

73 Posts

Posted - 2007-04-05 : 10:51:19

Thanks Everyone!!! All of these options seem to work.

I appreciate it!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources