| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12
Comments:
<0> I am using oracle sql and am trying to do a average of all the sums of 2 columns
<0> and am getting ORA-00937: not a single-group group function, but i am using the group by correctly i think
<1> average of the sums, as in attempting AVG(SUM(A) + SUM(B))
<1> ?
<0> AVG(sum(unit*price))
<1> you using groups or just want for the whole table?
<1> you must be on a 0.01 MBit/sec connection, cos' this convo is really slow
<0> well unit and price are in different tables, and its all connected by a unique id
<0> http://sql-servers.com/nopaste/?show=527
<0> sorry, i was copy and pasted into the website
<1> :)
<0> i just want to average for the whole table, but i thought I had to use a group by
<0> so i thew the ordernum in, which links the different tables
<1> no need for SUM
<1> http://sql-servers.com/nopaste/?show=528
<1> just average it
<1> wait; you just want for the whole table, not per order?
<1> http://sql-servers.com/nopaste/?show=529
<1> it originally sounded like you wanted the average line item subtotal per order
<1> just do avg(unit*price)
<0> i was looking for the overall average of an order
<1> grrr
<1> [22:43] <0> i just want to average for the whole table, but i thought I had to use a group by
<1> now you're starting to speak in circles man
<0> i thought the average for the whole table would be one value
<1> you want the average line item subtotal for each order?
<0> sorry if I am confusing
<0> no, the average total across all the totals
<0> so trying to add each of the orders up and take the average
<1> so you want the average of the "Order Total" then?
<0> yes
<1> http://sql-servers.com/nopaste/?show=530
<2> wut up
<1> aargh; i'm getting tired frank
<1> http://sql-servers.com/nopaste/?show=531
<1> that's it
<1> hi SicWork
<0> thanks, i do get an error on the last as though
<1> what is the error?
<0> ORA-00933: SQL command not properly ended
<1> put a semicolon then
<2> um.. just take the query for derived table and replace sum by avg?
<0> i added a semicolon
<2> ur grouping by ordre num 2 times?
<1> I GAVE YOU A NEW URL BECAUSE I MADE A MISTAKE
<2> because the first one (derived table) will give you unique ordernum
<1> [22:51] <1> aargh; i'm getting tired frank
<1> [22:51] <1> http://sql-servers.com/nopaste/?show=531
<1> [22:51] <1> that's it
<0> and thats the one i am using
<1> there is no second group by there
<2> yes tehre is
<1> WTF
<2> o u mean the new one
<0> i am using the new one and it gives that error
<2> do u even need htat outer one?
<1> yes, the latest one, the only one we should be discussing
<1> YES
<1> He wants an average of the sums
<2> cant u just replace hte sum with avg?
<1> no
<1> because the sums are order sums, and wants the average order total
<1> not the average line item total
<2> o ok sorry i dind see where u took the order by out
<2> thought u took the group by out of the derived one
<2> btw.. the PM took us out and i got drunk :)
<1> heh
<1> frankrizz, is it working now?
<0> nope, it gives the same error on the new link
<1> can you show me the syntax you are currently using?
<0> i copy and pasted in exactly with the addition of a ;
<2> how about: select ((select sum(unit*price) from ... )/(select count(*) from ... group by ordernum) )as avg
<2> shouldnt that give the same answer?
<1> yes
<1> try http://sql-servers.com/nopaste/?show=532
<0> that worked!
<0> thanks
<2> http://sql-servers.com/nopaste/?show=533
<2> hehe
<2> i added a t
<1> f**king oracle
<2> **** im drunk
<2> :0
<0> and i actually learned, thanks
<1> yw
<0> what you drinking?
<2> hmmm
<2> i dont remember
<2> but i know i can still sql ;)
<1> haha
<0> haha
<1> why the hell you can't "AS" the derived table....well
<2> cause u cant do that
<2> even in sql92
<2> you as the alias and you just ____ the derived table
<2> ok that really did make sense in my head
<1> what are you trying to say (I understand you are drinking, but still)
<2> you dont need (cant use) an AS for derived tables
<1> dont need?
<1> so if you have 3 derived tables, and want to join them, what's the syntax?
<2> no its always: select blah from (select ...) a, sometable....
<1> ok, so you are still using the alias, just no "AS"
<2> select * from (select .. ) a, (select .... ) b where a.id = b.id
<2> correct
<1> even postgresql supports AS
<1> oracle is just being stubborn
<2> i think ansi sql 92 u dont use AS
<2> mssql doesnt support it either
<1> yes it does
<3> For table aliasing? Sure it does
<4> has anyone here experienced bugs with sql server 2000 when exporting view and stored procedures? it seems to be scripting the original ones i wrote months ago and not the current versions that have been modified.....
<1> because you didn't rename them properly; not a bug
<2> ok fine i guess what i meant to say is that it works without the AS :)
<1> SicWork, it originally sounded as if you were saying there was no need to alias the table, which was laughable
<1> I get you now
<2> leave hte drunk guy alone
<1> Azwar, if you rename a stored procedure or view, the scripts are still pulled from syscomments; you're better off doing DROP/CREATE
<1> or else you have to manually edit them with EM after the rename
<2> or you just do: update syscomments set text = replace(text, 'newname', 'oldname')
<2> :)
<1> not recommended lol
<4> ok thanks fellas
<2> well use at ur own risk
<2> better yet.. truncate table syscomments
<2> delete all traces of the old ones ;)
<2> hmm i can definately see myself getting some poor idiot to drop their pruduction tables :)
<1> heh
<1> one time a guy was having trouble connecting to an instance of mysql that was exposed direct to teh internets....he actually gave up the username and p***word on the first request, right in the channel
<1> everybody started screaming "HURRY! CHANGE IT!"
<2> i wouldve been like.. "hold on let me try to get you the connection string"
<2> :)
<1> hehe
<1> "here, add OR 1=1 to that delete statement; fix you right up"
<1> there was a guy on one of the channels earlier today that deleted his master.ldf to free up space
<2> totally understandable thing
<2> :)
<1> :)
<2> i sold my crack pipe to get money to buy more crack...
<1> haha
<1> you ever read "Hey, Crackhead" on craigslist?
<2> ?
<1> http://www.craigslist.org/about/best/sfo/27499971.html
<1> funniest post ever
<2> ooo theres a sql-2003 standard
<2> i dindt know that
<2> [x] means 0 or 1 right?
<1> in what context?
<2> like when giving language defitions
Return to
#sql
or
Go to some related
logs:
iljazira
string to cardinal
#winxp
chan_mobile
datagridview row databounditem nothing vb.net
#politics
#hardware
taking expired tylenol
Entremetrix
#stocks
|
|