SQL Query 1

[aarellano]

Hello,

I am not sure if i am in the right place.

I am building an sql query where I have a department, class, item num, cost, direct hrs, indirect hrs.

What I would like to build is a summary by department and then by class, can this be done

department   class   itemnum  cost  direct hrs indirect hrs
43            ab       124     .26    1.17         2.1
43            ac       120     .27    1.25         2.3
43            ab       125     .24    1.21         2.3
43            ad       128     .23    1.0          2.1
43            aa       122     .25    1.2          0
43            ab       123     .25    1.2          0


44            ab       124     .26    1.17         2.1
44            ac       120     .27    1.25         2.3
44            ab       125     .24    1.21         2.3
44            ad       128     .23    1.0          2.1
44            aa       122     .25    1.2          0
44            ab       123     .25    1.2          0


45            ab       124     .26    1.17         2.1
45            ac       120     .27    1.25         2.3
45            ab       125     .24    1.21         2.3
45            ad       128     .23    1.0          2.1
45            aa       122     .25    1.2          0
45            ab       123     .25    1.2          0

into

Dpt     class    direct hrs   indirect hrs

43      aa       xxx              xxx
43      ab        xx               xx
43      ac    
43      ad        xx               xx


Dpt     class    direct hrs   indirect hrs

44      aa       xxx              xxx
44      ab        xx               xx
44      ac    
44      ad        xx               xx


Dpt     class    direct hrs   indirect hrs

44      aa       xxx              xxx
44      ab        xx               xx
44      ac    
44      ad        xx               xx

thanks

 

[aarellano]

this is what I have so far

SELECT
AVG(@DPTNO),
@ITCL,
SUM(DQTYRC),
SUM(DQTSCP),
((dqtscp/dqtyrc)*100) AS scrprc,
SUM(DTTACT),
SUM(DWRKST),
((DWRKST/DTTACT)*100) AS efficprc,
SUM(DWRKRS),
SUM(((DWRKRS-DWRKST)*100)) AS lossinscr,
EFFPR1,
EFFPR2
FROM DAVIESF.DPTTOTFL
GROUP BY @ITCL, ((dqtscp/dqtyrc)*100), DTTSTD, ((DWRKST/DTTACT)*100), EFFPR1, EFFPR2
ORDER BY @DPTNO, @ITCL, @ORDER

but this does not seem to be working

 

[lespaul]

Does this not work for you? I guess you'll have to change the field names since your sample SQL doesn't match your provided sample data field names.....why would you average the department number anyway?

SELECT Dpt, class, SUM(direct hrs), SUM(indirect hrs)
FROM TableName
GROUP BY Dpt, class

and is this Access? doesn't look like JetSQL.....

Leslie

Have you met Hardy Heron?

 

[BigRed1212]

I would think the pseudo code of

select dpt, class, sum(direct hours), sum (indirect hours)
from daviesf.dpttotfl
group by dpt, class

would generate the simplified output you show.

I'm not sure I follow or know enough about some of the other fields and stuff going on in the query you posted.

 

[aarellano]

lespaul,
I looked at the way you started to build the sql statement, this is what wrote

SELECT
@DPTNO,
@ITCL,
SUM(DQTYRC),
SUM(DQTSCP),
((SUM(DQTSCP)/SUM(DQTYRC))*100) AS scrprc,
SUM(DTTACT),
SUM(DWRKST),
((SUM(DWRKST)/SUM(DTTACT))*100) AS efficprc,
SUM(DWRKRS),
SUM(((DWRKRS-DWRKST)*100)) AS lossinscr
FROM DAVIESF.DPTTOTFL
GROUP BY @DPTNO, @ITCL
ORDER BY @DPTNO, @ITCL



This works just the way I wanted it.


Thank you!!!!!!

now the only problem is that for my calculations

((SUM(DQTSCP)/SUM(DQTYRC))*100) AS scrprc and

((SUM(DWRKST)/SUM(DTTACT))*100) AS efficprc


I do not see any decimals