[es] - Podupit i group by

Vlatko Zdrale
Vlatko Zdrale
Chicago, USA

Član broj: 155036
Poruke: 102
*.177.123.201.ip.anet.com.

Sajt: www.zemoon.com

+1 Profil

Podupit i group by

^{14.11.2007. u 15:57 - pre 200 meseci}

Code:

SELECT ProdID, SUM(qty) AS SubTotal, (SELECT SUM(qty) FROM OrderItem) AS Total
FROM OrderItem
GROUP BY ProdID
ORDER BY SubTotal DESC

i to mi daje nesto kao ovo:

Code:

ProdID SubTotal Total
15    10000 100000
6      8000  100000
9      6000  100000

meni bi trebalo da mi vraca procentualan udeo SubTotal u Total, i pokusao sam to ovako:

Code:

SELECT ProdID, SUM(qty) AS SubTotal, (SELECT SUM(qty) FROM OrderItem) AS Total, [b]SubTotal / Total * 100 AS Percentage[/b]
FROM OrderItem
GROUP BY ProdID
ORDER BY SubTotal DESC

ali mi SQL Server daje gresku: Invalid column name 'SubTotal'
U cemu je problem? MS SQL? (Cini mi se da je to MySQL server gutao)

Odgovor na temu

chachka
Srđan Mijatov
Programer
BUS Computers
Kikinda

Član broj: 53780
Poruke: 576
*.ADSL.neobee.net.

Sajt: www.baze-podataka.net

+4 Profil

Re: Podupit i group by

^{14.11.2007. u 19:26 - pre 200 meseci}

Code:
SELECT ProdID, SUM(qty) AS SubTotal, (SELECT SUM(qty) FROM OrderItem) AS Total,
SUM(qty) / (SELECT SUM(qty) FROM OrderItem) * 100 AS Percentage
FROM OrderItem
GROUP BY ProdID
ORDER BY SubTotal DESC

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Odgovor na temu

Vlatko Zdrale
Vlatko Zdrale
Chicago, USA

Član broj: 155036
Poruke: 102
*.177.123.201.ip.anet.com.

Sajt: www.zemoon.com

+1 Profil

Re: Podupit i group by

^{14.11.2007. u 20:14 - pre 200 meseci}

Hvala na resenju, delimicno mi je pomoglo :) Prvo mi je davao 0 za Percentage, ali sma pretpostavio da je to zbog integer vrednosti, pa sam promenio u :

Code:

SELECT     ProdID, SUM(qty) AS SubTotal,
                          (SELECT     SUM(qty)
                            FROM          OrderItem) AS Total, SUM(qty) * 100 /
                          (SELECT     SUM(qty)
                            FROM          OrderItem) AS Percentage
FROM         OrderItem
GROUP BY ProdID
ORDER BY SubTotal DESC

ali daje mi cele brojeve za procente, a posto imam dosta proizvoda, vecina procenata je manja od 1%. Jel postoji neki nacin da mu kazem da racuna kao float? Pokusavao sma sa cast (nesto, float) ali netje da sljaka.

Odgovor na temu

Miloš Baić
Miloš Baić
ERP (Dynamics NAV) programer
Beograd

Član broj: 72468
Poruke: 1155
*.kalik.info.

Profil

Re: Podupit i group by

^{15.11.2007. u 01:18 - pre 200 meseci}

Možda ovo pomogne:

Code:

/* Table: OrderItem */

create table OrderItem (
ProdID integer,
qty    numeric(16,2)
);

/*Punjenje tabele OrderItem */

insert into OrderItem values(1, 500);
insert into OrderItem values(1, 500);
insert into OrderItem values(2, 1000);
insert into OrderItem values(2, 1000);
insert into OrderItem values(3, 3500);
insert into OrderItem values(3, 3500);
insert into OrderItem values(4, 500.36);
insert into OrderItem values(4, 256.84);

/*Upit nad tabelom OrderItem */

select t1.ProdID,
       t1.Sub_total,
       t2.Total,
       ROUND(t1.Sub_total / t2.Total * 100, 4) as Percentage
from
  ( select ProdID,
           sum(qty) as Sub_total
    from
       OrderItem
    group by
       ProdID ) t1,

  ( select sum(qty) as Total
    from
       OrderItem ) t2
order by
   t1.ProdID

Rezultat:

Someone's sitting in the shade today because someone planted a tree a long time ago.

Prikačeni fajlovi

slika.JPG - 11.68k

Odgovor na temu