8 Replies - 3883 Views - Last Post: 23 September 2012 - 09:41 PM

#1 DADDYCARDONA  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 04-July 10

SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 19 September 2012 - 06:54 AM

Hi I was working on a project from school, unfortunately they use server 2008 and on my computer I can only use 2012. My team mate made some code and I was trying to help out but came across this problem of trying to convert what he wrote to display the same way as it did for him. Unfortunately this is my first encounter with SQL and I failed.:( So my question is how do I change:
    
   Use Kudler_Database
   SELECT  AccountNumber, [Description], ShortDescription,Balance
   FROM Chart_of_Accounts 
   ORDER BY left (AccountNumber, 2)
   COMPUTE SUM(Balance) BY left (AccountNumber, 2)
   COMPUTE SUM(Balance); 


into something that uses the roll up part. I tried substituting compute with rollup and it did not work. I also tried this
     SELECT AccountNumber, [Description], ShortDescription,Sum(Balance)
     FROM FROM Chart_of_Accounts 
     ORDER BY left (AccountNumber, 2)
     WITH ROLLUP


This post has been edited by DADDYCARDONA: 19 September 2012 - 06:55 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9581
  • View blog
  • Posts: 36,317
  • Joined: 12-June 08

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 19 September 2012 - 07:16 AM

Quote

something that uses the roll up part

Quote

I tried substituting compute with rollup and it did not work.


No one knows what 'roll up' means in this case - you did not provide any sort of context. The same goes for 'compute' - zero context.

How about explain the table layout and what you are trying to accomplish.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 498
  • View blog
  • Posts: 3,317
  • Joined: 12-January 10

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 19 September 2012 - 07:18 AM

I have never heard of roll up---- and why do you have 2 "from" in your query
Was This Post Helpful? 0
  • +
  • -

#4 DADDYCARDONA  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 04-July 10

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 21 September 2012 - 09:34 AM

View PostDarenR, on 19 September 2012 - 07:18 AM, said:

I have never heard of roll up---- and why do you have 2 "from" in your query
First of all it exist, it is used in sever 2012 and compute no longer is. Second of all ROLLUP is suppose to take the place of COMPUTE and COMPUTE BY according to Microsoft. Sorry for the late response I don't have internet and my compute crashed so I had to buy a new one. I also inserted server 2008 in it so I can use this code now. :)

View Postmodi123_1, on 19 September 2012 - 07:16 AM, said:

Quote

something that uses the roll up part

Quote

I tried substituting compute with rollup and it did not work.


No one knows what 'roll up' means in this case - you did not provide any sort of context. The same goes for 'compute' - zero context.

How about explain the table layout and what you are trying to accomplish.

I thought the example of the code would be sufficient and I thought that there were SQL coders that would know because it is used in server 2012 and compute no longer is. The 2 I don't know my buddy wrote it and it works in server 2008 so I am going along with it. :) I don't worry about it no more because like the comment before I am good now. :)

This post has been edited by DADDYCARDONA: 21 September 2012 - 09:36 AM

Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5941
  • View blog
  • Posts: 12,870
  • Joined: 16-October 07

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 21 September 2012 - 10:59 AM

COMPUTE and ROLLUP are wonky little SQL Server extensions. Never had cause to use them, though.

Found a pretty good write up here: http://sqlwithmanoj....-grouping-sets/
Was This Post Helpful? 1
  • +
  • -

#6 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 21 September 2012 - 09:26 PM

i think just a little change in your query will work
SELECT AccountNumber, [Description], ShortDescription, sum(Balance) AS Balance
FROM   Chart_of_Accounts
GROUP BY
LEFT (AccountNumber, 2),AccountNumber, [Description], ShortDescription
WITH ROLLUP 


after having the result just remove the unwanted rows using CTE or a Temporary table
hope this work for you
Was This Post Helpful? 1
  • +
  • -

#7 DADDYCARDONA  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 04-July 10

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 21 September 2012 - 10:35 PM

View Postthava, on 21 September 2012 - 09:26 PM, said:

i think just a little change in your query will work
SELECT AccountNumber, [Description], ShortDescription, sum(Balance) AS Balance
FROM   Chart_of_Accounts
GROUP BY
LEFT (AccountNumber, 2),AccountNumber, [Description], ShortDescription
WITH ROLLUP 


after having the result just remove the unwanted rows using CTE or a Temporary table
hope this work for you

This worked even though I did not need it anymore it did work thanks a lot. How do you accept your answer again?
Was This Post Helpful? 0
  • +
  • -

#8 DADDYCARDONA  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 04-July 10

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 21 September 2012 - 10:44 PM

I like your quote as well, have you heard this one yet

Quote

Nothing is impossible, it says it in the word. Im possible

Was This Post Helpful? 0
  • +
  • -

#9 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: SQL COMPUTE and COMPUTE BY transfer to ROLLUP SCRIPT

Posted 23 September 2012 - 09:41 PM

yes, i heard it
Posted Image
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1