How to Group By all columns but have Rollup only on few columns - Database Discussion Boards

Re: select statement to display name 1000 times

Ashfield1-Dec-09 0:09

Ashfield

1-Dec-09 0:09

use the same method as you were told previously, a cartesian join, and limit it

select top 1000 'a' 
from dbo.sysobjects as so1, dbo.sysobjects as so2

and buy a book on SQL.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: select statement to display name 1000 times

arun_pk1-Dec-09 0:22

arun_pk

1-Dec-09 0:22

thanks for ur advice
but u are failed to answer my q
u buy SQL basics

Re: select statement to display name 1000 times

Ashfield1-Dec-09 0:25

Ashfield

1-Dec-09 0:25

arun_pk wrote:
but u are failed to answer my q

In what respect?

select top 1000 'a' from dbo.sysobjects as so1, dbo.sysobjects as so2

will do as your (vague) question asks.

arun_pk wrote:
u buy SQL basics

Why? Its not me asking basic questions about sql, its you that doesn't understand what you are being told will work.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: select statement to display name 1000 times

Shameel1-Dec-09 7:05

Shameel

1-Dec-09 7:05

arun_pk wrote:
but u are failed to answer my q

Ashfield's query works perfectly for your requirement.

arun_pk wrote:
u buy SQL basics

I just saw you being spoon fed by Niladri_Biswas and you are advising others to learn SQL basics ? Funny.

Re: select statement to display name 1000 times

Mycroft Holmes1-Dec-09 17:04

Mycroft Holmes

1-Dec-09 17:04

Cute - what if sysobjects less than 100 records, add another cross join, and another Laugh | :laugh:

Never underestimate the power of human stupidity
RAH

Re: select statement to display name 1000 times

Ashfield1-Dec-09 21:37

Ashfield

1-Dec-09 21:37

Mycroft Holmes wrote:
what if sysobjects less than 100 records, add another cross join

Yep. Mind you, by default sysobjects has about 40 rows, and 40 * 40 is 1600 so he should be safe.

And at the end of the day, he now has enough info to work it out for himself Smile | :)

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: select statement to display name 1000 times

Mycroft Holmes1-Dec-09 21:55

Mycroft Holmes

1-Dec-09 21:55

Ashfield wrote:
he now has enough info to work it out for himself

Not with the spoon feeding Niladri had to do, I doubt he has the nous to research cross join!

Never underestimate the power of human stupidity
RAH

How to Group By all columns but have Rollup only on few columns

guydebell30-Nov-09 23:10

guydebell

30-Nov-09 23:10

I am new to SQL coding and having trouble grouping and generating sub totals and Grand Totals for a report generated by this query:

SELECT ISNULL(CAST(dteAccountingDate AS varchar (20)),'') AS MonthEnd,Payee, ClientName,
ISNULL (CAST (Accrual as varchar (20)),'') AS Accrual,
ISNULL (CAST (Payment as varchar (20)),'') AS Payment,
ISNULL (CAST (OverUnder as varchar (20)),'') AS OverUnder,
ISNULL (CAST (Salaries as varchar (20)),'') AS AnnualisedSalaries,
ISNULL (CAST (PercentVal as varchar (20)),'') AS Percentage,
ISNULL (CAST (CountMem as varchar (20)),'') AS NoMember,
ISNULL (CAST (CostPerMem as varchar (20)),'') AS CostPerMember

FROM #tmp INNER JOIN tblClientParents
ON #tmp.fkiClientParentID = tblClientParents.pkiClientParentID
AND Payee NOT IN ('Opening Value','Current Year','Sub Totals','Totals', '','______________','Prior Year Payment')

My primary objective is to have results grouped and summaries generated by Payee and ClientName.
I tried group by and order by but they don't give me the required results; even tried Compute By. I want to use Group by With Rollup but the problem is i only want to generate summaries of three columns Accrual, Payment and CountMem.

Is there a way to Group by all the columns in the select query but have Rollup only on the three columns? Or any other ways to generate summaries on the three columns only?

I also tried Group By Rollup, it doesn't work with SQL Server 2008 that i am using.

Need some help here please!

Thanks in advance

Re: How to Group By all columns but have Rollup only on few columns [modified]

Niladri_Biswas30-Nov-09 23:33

Niladri_Biswas

30-Nov-09 23:33

This will help...

GROUP BY, CUBE, ROLLUP and SQL SERVER 2005
Smile | :)

Niladri Biswas

modified on Tuesday, December 1, 2009 7:59 AM

Re: How to Group By all columns but have Rollup only on few columns

guydebell1-Dec-09 1:05

guydebell

1-Dec-09 1:05

Hi Niladri

Thanks for the article, it gives me a better understanding of Grouping and Rollup. I applied it by inserting Case Grouping on Payee and ClientName but still didn't yield the desired results. May be i don't grasp it quite clearly, but you see in the example in the article they had two columns (CustomerName and ItemName), so when grouping they just group by the two columns. In my case i have 10 columns and only have to summarise 3 columns (Payment, Accrual, CountMem) and group by Payee and ClientName only.

Please help me out here.

How to connect/disconnect users in sql server.

Ashish Kumar Vyas30-Nov-09 20:13

Ashish Kumar Vyas

30-Nov-09 20:13

You have to check whether any users are connected to sql server database and if any user is connected to database, you have to disconnect the user(s) and run a process in a job. How do you do the above in a job?

Re: How to connect/disconnect users in sql server.

Ashfield30-Nov-09 20:57

Ashfield

30-Nov-09 20:57

Sounds like a homework question, but.....

Take a look at SP_WHO and KILL, but be warned, you need admin (sa) rights to do a kill.

Ashish Kumar Vyas wrote:
run a process in a job.

not sure what you mean here. Run some sql? run a stored proc? what?

anyway, hopefully this will point you in the right direction.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

inserting multiple rows

arun_pk30-Nov-09 18:07

arun_pk

30-Nov-09 18:07

how to insert multiple rows in sql

using a single sql command

thanks in advance
arun

Re: inserting multiple rows

_Damian S_30-Nov-09 18:18

_Damian S_

30-Nov-09 18:18

It depends where the data is coming from... Can you be a little more specific?

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!!
Do something wonderful - make a donation to Camp Quality today!!

Re: inserting multiple rows

arun_pk30-Nov-09 18:21

arun_pk

30-Nov-09 18:21

thanks for the response

i wanted to add around 500000 rows of data in a table as atemplate for my project

one field can be the primary key which will be auto incremented

all others are repeated values...

so is it possible for me to add multiple rows at one shot

Re: inserting multiple rows

dan!sh 30-Nov-09 18:32

dan!sh

30-Nov-09 18:32

If you have all of that data in a table, you can use:

Insert into DataBaseTable 
select * from SourceTable

You can also use Bulk Insert[^] for doing this.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: inserting multiple rows

arun_pk30-Nov-09 18:36

arun_pk

30-Nov-09 18:36

i dont have a alternate table
____ let me give u a clear picture

lets assume my table is details with coloumns ID(primary key)(Auto incriment), Name

so my insert statement is INSERT INTO DETAILS VALUES("abcd");

i want the same data 500000 times

do i need to run it in a loop
or is there any other method of adding multiple rows

Re: inserting multiple rows

_Damian S_30-Nov-09 18:37

_Damian S_

30-Nov-09 18:37

See my cartesian product answer below.

Re: inserting multiple rows

_Damian S_30-Nov-09 18:37

_Damian S_

30-Nov-09 18:37

I figured he had no data, but wanted 500K records to test with...

Re: inserting multiple rows

dan!sh 30-Nov-09 18:57

dan!sh

30-Nov-09 18:57

My bad. I thought he needs to insert data from somewhere to his DB.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: inserting multiple rows

_Damian S_30-Nov-09 18:36

_Damian S_

30-Nov-09 18:36

The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination).

The following select statement will return over a million rows on my test SQL Server:

select 'a' 
from dbo.sysobjects as so1, dbo.sysobjects as so2

Extending this into an insert statement, you would do this:

insert into TABLENAME(FIELD1, FIELD2)
select 'a', 'b'
from dbo.sysobjects as so1, dbo.sysobjects as so2

which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with.

Hope this helps.