Podupit i group by

[ Vlatko Zdrale @ 14.11.2007. 15:57 ] @

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)

[ chachka @ 14.11.2007. 19:26 ] @

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

[ Vlatko Zdrale @ 14.11.2007. 20:14 ] @

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.

[ Miloš Baić @ 15.11.2007. 01:18 ] @

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:
[att_img]