sum

sum

Post by alexqa2.. » Tue, 26 Aug 2003 21:30:31



Trying to obtain total number of books per class along with other fields.

classA 1 fname1  8  ...
classA 2 fname2  3  ...
classA 3 fname3  4  ...

How to modify the query in below to return all fields and sum number
of books also?.

select *
#tempbook
group by class,order,name,Books,...

should get total of 15 books in this case.

--
Sent by 3 from yahoo part  from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

 
 
 

sum

Post by Anith Se » Tue, 26 Aug 2003 21:53:43


Do:

SELECT col1, col2, ...,
       (SELECT SUM(nbr)
          FROM tbl t1
         WHERE t1.col1 = tbl.col1) AS "total"
  FROM tbl ;

--
- Anith
( Please reply to newsgroups only )

 
 
 

sum

Post by Robert Taylo » Tue, 26 Aug 2003 22:04:02


One of the limitations of aggregate functions is that the more columns
you choose to display, the more you break down your aggregate columns
into smaller subtotals.  If what you are trying to accomplish is truly a
subtotal by class then try this...

select class,sum(books)
group by class

If you need to keep all of the data and still keep the grand totals then
try ...

select * from
(select class,sum(books) as gtotal
   from #tmpBook
   group by class) a
inner join b ON a.class=b.class
(select * from #tmpBook) b

HTH,

Robert

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Sum(Sum(Sum ...

Maybe subject isn't so clear, but I think that it's the best way to
let people understand what I want.

I need to call nested sum into a calculated field that contain
another sum function. Below there is the query I need to run.

I think that the only way to make what I need, is to run query
into a cursor, then cicle it and summing value into appo variable.
But maybe I missing something.

    SELECT DBO.FORMATTAIMPORTO(CAST(ROUND((B.PRD_COSTO * (100 * A.PRD_QTA /
SUM(C.PRD_QTA)) / 100), 2) AS DECIMAL(19, 2)), '') AS PRD_COSTOQTA
    , DBO.FORMATTAIMPORTO(CAST(ROUND(DBO.CALCOLA_RICARICHI(((B.PRD_COSTO *
(100 * A.PRD_QTA / SUM(C.prd_qta)) / 100)), B.PRD_DELTA, B.PRD_COSTOGEST,
0), 2) AS DECIMAL(19, 2)), '') AS PRD_COSTOQTA_DELTACOSTOG
    , DBO.FORMATTAIMPORTO(CAST(ROUND(DBO.CALCOLA_RICARICHI(((B.PRD_COSTO *
(100 * A.PRD_QTA / SUM(C.prd_qta)) / 100)), B.PRD_DELTA, B.PRD_COSTOGEST,
B.PRD_UTILE), 2) AS DECIMAL(19, 2)), '') AS PRD_COSTOQTA_DELTACOSTOUTILEG
    FROM V_PREVDETT AS B JOIN V_PREVDETT AS A ON A.PRD_IDDETTMC =
B.PRD_IDBENSRV
    JOIN V_PREVDETT AS C ON C.PRD_IDDETTMC = A.PRD_IDDETTMC
    WHERE A.PRD_TIPOBENE IN ('M', 'DM')
    AND B.PRD_IDPREV = 32
    AND A.PRD_IDDETTMC = 57
    AND B.PRD_IDPREV = A.PRD_IDPREV
    AND C.PRD_IDPREV = A.PRD_IDPREV
    GROUP BY A.PRD_CODMAG
    , A.PRD_QTA
    , B.PRD_COSTO
    , B.PRD_DELTA
    , B.PRD_COSTOGEST
    , B.PRD_UTILE
    , A.PRD_TIPOBENE
    , B.PRD_DESCRBENE
    , A.PRD_TIPOBENE

--
Thanks for any help or suggestion provided.
Andrea Moro

2. Set queries with multiple keys

3. Which is faster Sum(fld1) + Sum(fld2) +...Sum(fld50) -- OR this

4. Anybody using Oracle Software Manager?

5. Performing a sum on a sum

6. Urgent - Need Help!

7. Q: how to SUM the SUM?

8. Sybase programers wanted DC area.

9. sum of a sum

10. Getting a sum of a sum...

11. How to get the sum of Sums...

12. Sum Of SUM

13. SQL Plus -- need detail lines and sum of Amount, if the sum is not 0