Engineers Make Lousy Husbands

An engineer is trained to think about all the possibilities for a system problem.  Whether it is a hardware or software system, all the reasons for a hiccup or failure must be identified in order to resolve the problem.  This results in several hypotheses and explanations.

rock_climber

While engineers may be great problem solvers, they fail to get a grip on how to communicate.

When an engineer tackles one of life’s problems, many possible solutions are examined.  Like which do you do first – pay the bills, buy groceries or fix the garden?  And in what order do you perform the rest of these tasks?  An engineer would give several action plans and reasons for each.

But a wife thinks a lot differently than an engineer.  In fact a woman will want one and only one answer to a question or issue or she will begin to distrust the husband.

So try to imagine being an engineer AND a husband.  What a conundrum.

She gets upset when he gives her more than one answer and then he gets frustrated when he realizes he should have stuck with the first answer.  Talk about being up the creek without a paddle.  Oy vey!

Sybase SQL Challenge – Sum Of Max Values

--------DATA in Cat_Tbl---------------------------
Cat1           Cat2          Value
a              e1            113
b              e1            14
a              e2            15
c              e3            13
a              e1            13
a              e2            11
c              e4            1

Sybase

PROBLEM:

For each distinct value in Cat1, user wants to find maximum of Values grouped by Cat2 and then sum them up. 

 

 

Step 1: User wants to extract the following rows for example where Cat1 = ‘a’.

Cat1 Cat2 Value 
a    e1   113 
a    e2   15 
a    e1   13 
a    e2   11

Step 2: User wants to extract maximum corresponding to Cat1 and Cat2:

Cat1 Cat2 Value 
a    e1   113 
a    e2   15

Step 3: User wants to sum the Values for Cat1.

Cat1 SumOfValueColumn 
a    128 
b    14 
c    14

stackoverflow

 

 

 

 

SOLUTION 1 USING TEMPORARY TABLE #Max_Cat_Tbl

select Cat1, Cat2, max(Value) Maxvalue 
into #Max_Cat_Tbl from Cat_Tbl 
group by Cat1, Cat2 order by 1, 2 
select Cat1, sum(Maxvalue) 
from #Max_Cat_Tbl 
group by Cat1 
order by Cat1 
drop table #Max_Cat_Tbl 
go

SOLUTION 2 USING VIEW Max_Cat_Tbl_View

create view Max_Cat_Tbl_View 
( Cat1, Cat2, Maxvalue ) as 
select Cat1, Cat2, max(Value) 
from Cat_Tbl 
group by Cat1, Cat2 
select Cat1, sum(Maxvalue) 
from Max_Cat_Tbl_View 
group by Cat1 
order by Cat1 
go

Good SQL, good night.