Getting a field from subtotal

hi all,

i have a problem in my report; some fields grouped by matrix and include a subtotal. In column group there is 2 field and one of them contain integer values. I want to sum all of the this fields and minus from a rowgroup field. Is there a way for to sum fields in coloumn group or getting total field from subtotal?

I'm having trouble understanding your problem and your English. Could you try to rephrase your question and add a little more detail?

Thanks

Get with the program

It is very interesting to me that most people in this group can't do simple
SQL. Are you viewing MySQL as a simple file system?As long as you do that,
you will
not understand SQL in any dialect. So from now on:

When you have a question about SQL. post the table structures. Uh, "With
Create".
Post some sample data in the form of INSERTS.

Regards,
Rich

--
The journey is the reward."Rich R" <rryan@.cshore.com> wrote in message
news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
> It is very interesting to me that most people in this group can't do
simple
> SQL. Are you viewing MySQL as a simple file system?As long as you do that,
> you will
> not understand SQL in any dialect. So from now on:
> When you have a question about SQL. post the table structures. Uh, "With
> Create".
> Post some sample data in the form of INSERTS.
>
> Regards,
> Rich
> --
> The journey is the reward.

Sorry, wrong group. Hate when that happens. Please ignore.

Regards,
Rich|||"Rich R" <rryan@.cshore.com> wrote in message
news:0AhHd.19291$by5.12203@.newssvr19.news.prodigy. com...
> "Rich R" <rryan@.cshore.com> wrote in message
> news:IxhHd.19290$by5.3314@.newssvr19.news.prodigy.c om...
>> It is very interesting to me that most people in this group can't do
> simple
>> SQL. Are you viewing MySQL as a simple file system?As long as you do
>> that,
>> you will
>> not understand SQL in any dialect. So from now on:
>>
>> When you have a question about SQL. post the table structures. Uh, "With
>> Create".
>> Post some sample data in the form of INSERTS.
>>
>>
>> Regards,
>> Rich
>>
>> --
>> The journey is the reward.
>
> Sorry, wrong group. Hate when that happens. Please ignore.
> Regards,
> Rich

That's alright, its a universal issue, just ask --CELKO--

Get Unique Values in a group statement

Hi,
Suppose a table [Inventory]:

Item Color Quantity
------- ------- --------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:

Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis PerrakisDoes anyone has an idea how this can be achieved?Ooh ooh ooh - me - pick me!

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
:)|||Hi,
thanks for the answer.
I don't think is a good idea to run a query for each record. This will consume a lot of resources, and in my case when having to do with large tables and a lot of resulted records this is not an option.
I was hoping there was an internal function, of if it can be defined such a funtion. For example if AVG is used the MSSQL access all the records keep their values and at the end calculates the result. Instead of adding these values I was hoping to create a string which can be compared internally without having to execute another query.
Reagrds,
Manolis Perrakis|||Search the text for "Yep, me too... until I tuned it and then it did it all in 5 seconds flat on a million rows for 50,000 CustID's. " and see if the suggested optimisations are appropriate for you.

Ultimately you are taking relational data and trying to put it into a context that violates first normal form so it is not surprising that SQL does not provide a built in function to do this.

The other alternative is that you can write (if you are using SQL Server 2005) your own CLR aggregate functions. I don't really know anything about these (including how they perform) but you could try researching to see if it is applicable to your needs.|||Yes you are right. There is the article:
http://msdn2.microsoft.com/en-us/library/ms131056.aspx
that does exactly this.
However I use MSSQL2000.|||However I use MSSQL2000.I suspect you are probably stuffed then.

I like the article I linked to as it demonstrates, and compares, two methods of skinning this particular cat. In particular it focuses on performance and, as I pointed out, later on one contributer offers a few refinements that get a decent performance for a medium sized table (1 million rows).

I doubt you will find any alternative technique that will substantially outperform the solution in the article but maybe one of the posters here will surprise me :)

BTW - what sort of performance did you get?|||Hi,
I want to use this operation in some aggregate complex queries that are already slow. Apart from this in order to get the correct data when calculating these value I must run these queries with other criteria also, such as date range which must be used in order to get the correct records. Therefore it's quite complicated. However I will try it the next days.
Regards,
Manolis|||I don't suppose there is a finite, known and ideally small number of possible "color" values?|||No, the "color" values is large, about 25000.|||you can get these things in 25000 different colours? Henry Ford would turn in his grave...|||I don't think is a good idea to run a query for each record. Just thought - I don't know how SQL Server optimises the query for this but presumably does as you suggest - runs the function for each record and then groups on the results. So you could improve things (I imagine) with something like:

SELECT Item, dbo.MyConcatFunction(Item) AS CSV_Colors, TotalQuantity
FROM (
SELECT Item, SUM(Quantity) AS TotalQuantity
FROM [Inventory]
GROUP BY Item
) AS Distinct_Items
Call the function once per item rather than once per row.

Get the ID from Min(grouppingcolumn)

Hi,
I have a simple query,
select emp_group,min(salary) from employees group by emp_group.
now I get the results.
Is there a way to get the ID(Primary key Column) value for which the
query returned minimum salary.
Thanks
Kiranselect e.ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
Note that you may get multiple rows back for
a particular minimum salary in a group.|||Your result could be not unique as you are only getting the min salary
of a specific emp_group (which I assume is anattribute for
allemployees, so it could be that more than one employee has a specific
emp_group). Therefore you can only get the set of employees (could be
one or more) who have the min salary.
SELECT <columnlist>
FROM employees
WHERE salary IN --OR "="
(
select emp_group,min(salary) from employees group by emp_group
)
HTH, Jens Suessmeyer.|||Hi Kiran
you'll need to join the MIN query to the same table it's based on.
See the thread here:
http://groups.google.com/group/micr...b0e4da5f8f24d99
cheers
Seb|||markc600@.hotmail.com wrote:
> select e.ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> Note that you may get multiple rows back for
> a particular minimum salary in a group.
>
Thanks a lot, that's what I was looking for.
what if it returns multiple rows of min sal in a group and I want the top 1.
Thanks
Kiran|||top 1 based on what criteria?|||sebt wrote:
> Hi Kiran
> you'll need to join the MIN query to the same table it's based on.
> See the thread here:
> http://groups.google.com/group/micr...b0e4da5f8f24d99
> cheers
> Seb
>
Thanks|||markc600@.hotmail.com wrote:
> top 1 based on what criteria?
>
if a group has same min. slaray with two rows, I need the top 1.
based on e.ID
Thanks
Kiran|||select min(e.ID) as ID,e.emp_group,e.salary
from employees e
inner join (
select emp_group,min(salary)
from employees
group by emp_group) as X(emp_group,minsalary)
on e.emp_group=X.emp_group and e.salary=X.minsalary
group by e.emp_group,e.salary|||markc600@.hotmail.com wrote:
> select min(e.ID) as ID,e.emp_group,e.salary
> from employees e
> inner join (
> select emp_group,min(salary)
> from employees
> group by emp_group) as X(emp_group,minsalary)
> on e.emp_group=X.emp_group and e.salary=X.minsalary
> group by e.emp_group,e.salary
>
Thanks a lot Mark

Get some values from a group

Hello,

here's my problem. There's table t with:

year, id, price
2000 1 100
2000 1 200
2000 2 100
2000 3 500
2000 4 100
2001 1 100
2001 2 300
...

I need a way, to get those prices, so I have e.g. 3 Groups for every year with the same number of elements in it. Here's the bakground:
My employer wants a report for the sold objects in each year classified by upper, middle and lower price level. The biggest problem is, that I cannot define prices manually. What I have to do, is to order prices in each group (e.g. year), combine that with row_number(), devide row_count by 3, do a loop with step (row_number / 3) and get price at that position.
But getting those prices gives me the willies. I just discoverd the "MODEL" Clause. Do you think, that can help me solve the problem?
Maybe you have some tips.
Thanks!sorry, anything with "do a loop" in the requirements is not going to get a lot of responses on this site

we normally suggest that you restate your requirements in terms of what data you want, rather than how to get it (especially if how to get it involves cursors)|||nevermind, I think ntile is what i'm looking for ;-)|||The statistically correct way to do what you've described using sets is:CREATE TABLE #p (
yearID INT
, ID INT
, price MONEY
)

INSERT INTO #p (
yearID, ID, price
) SELECT 2000, 1, 100
UNION ALL SELECT 2000, 1, 200
UNION ALL SELECT 2000, 2, 100
UNION ALL SELECT 2000, 3, 500
UNION ALL SELECT 2000, 4, 100
UNION ALL SELECT 2001, 1, 100
UNION ALL SELECT 2001, 2, 300

SELECT a.yearID, a.ID, a.price
, 1 + Convert( INT, (SELECT Count(*)
FROM #p AS b
WHERE b.price < a.price)
/ (SELECT Count(*) / 3.0 FROM #p AS c))
FROM #p AS a

DROP TABLE #pNote that the conversion back to INT (forcing truncation) is very important from a statistical perspective.

-PatP|||Thank you Pat! I could make it with ntile

select year,pos,min(price) from
(
select
ntile(4) over(partition by year order by price) as pos,
year,
price
from
(select distinct id,year,price from #t ) as a
)as b
group by pos,year

This works fine, because I dont need to know anything about the data before. Anyway, this query doesn't look optimal. Is there a way to reduce selects?
Thx!

Get OS Group membership

Hello,
Im using the 'xp_cmdshell' to know all users that belong to the local
administrators group and i'm using the 'net use localgroup' to get the output
into my report.
Here is the code and the Output:
set nocount on
exec xp_cmdshell 'net use localgroup'
go
set nocount off
OUTPUT:
output
--
Alias name administrators
Comment Administrators have..
NULL
Members
NULL
--
Administrator
DOMAINXXX\Domain Admins
DOMAINXXX\usera
DOMAINXXX\userb
The command completed successfully.
NULL
NULL
Is there any way to avoid lines where appear:
output
--
Alias name administrators
Comment Administrators have..
NULL
and all the information that is not part of the group membership?
Do you know other way to get this information?
Thanks,
Best regardsIf the builtin\administrators group still has a login in SQL
Server (it is by default but you could remove it),
you can get the members of the local admin group with:
EXEC xp_logininfo 'BUILTIN\Administrators', 'MEMBERS'
Another would be using openquery with ADSI:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Fri, 11 Feb 2005 09:53:03 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>Im using the 'xp_cmdshell' to know all users that belong to the local
>administrators group and i'm using the 'net use localgroup' to get the output
>into my report.
>Here is the code and the Output:
>set nocount on
>exec xp_cmdshell 'net use localgroup'
>go
>set nocount off
>OUTPUT:
>output
>
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>Members
>NULL
>--
> Administrator
> DOMAINXXX\Domain Admins
> DOMAINXXX\usera
> DOMAINXXX\userb
>The command completed successfully.
>NULL
>NULL
>Is there any way to avoid lines where appear:
>output
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>and all the information that is not part of the group membership?
>Do you know other way to get this information?
>Thanks,
>Best regards
>

Get OS Group membership

Hello,
Im using the 'xp_cmdshell' to know all users that belong to the local
administrators group and i'm using the 'net use localgroup' to get the output
into my report.
Here is the code and the Output:
set nocount on
exec xp_cmdshell 'net use localgroup'
go
set nocount off
OUTPUT:
output
Alias name administrators
Comment Administrators have..
NULL
Members
NULL
Administrator
DOMAINXXX\Domain Admins
DOMAINXXX\usera
DOMAINXXX\userb
The command completed successfully.
NULL
NULL
Is there any way to avoid lines where appear:
output
Alias name administrators
Comment Administrators have..
NULL
and all the information that is not part of the group membership?
Do you know other way to get this information?
Thanks,
Best regards
If the builtin\administrators group still has a login in SQL
Server (it is by default but you could remove it),
you can get the members of the local admin group with:
EXEC xp_logininfo 'BUILTIN\Administrators', 'MEMBERS'
Another would be using openquery with ADSI:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Fri, 11 Feb 2005 09:53:03 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>Im using the 'xp_cmdshell' to know all users that belong to the local
>administrators group and i'm using the 'net use localgroup' to get the output
>into my report.
>Here is the code and the Output:
>set nocount on
>exec xp_cmdshell 'net use localgroup'
>go
>set nocount off
>OUTPUT:
>output
>
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>Members
>NULL
>--
> Administrator
> DOMAINXXX\Domain Admins
> DOMAINXXX\usera
> DOMAINXXX\userb
>The command completed successfully.
>NULL
>NULL
>Is there any way to avoid lines where appear:
>output
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>and all the information that is not part of the group membership?
>Do you know other way to get this information?
>Thanks,
>Best regards
>

Get OS Group membership

Hello,
Im using the 'xp_cmdshell' to know all users that belong to the local
administrators group and i'm using the 'net use localgroup' to get the outpu
t
into my report.
Here is the code and the Output:
set nocount on
exec xp_cmdshell 'net use localgroup'
go
set nocount off
OUTPUT:
output
Alias name administrators
Comment Administrators have..
NULL
Members
NULL
--
Administrator
DOMAINXXX\Domain Admins
DOMAINXXX\usera
DOMAINXXX\userb
The command completed successfully.
NULL
NULL
Is there any way to avoid lines where appear:
output
--
Alias name administrators
Comment Administrators have..
NULL
and all the information that is not part of the group membership?
Do you know other way to get this information?
Thanks,
Best regardsIf the builtin\administrators group still has a login in SQL
Server (it is by default but you could remove it),
you can get the members of the local admin group with:
EXEC xp_logininfo 'BUILTIN\Administrators', 'MEMBERS'
Another would be using openquery with ADSI:
INFO: Performing a SQL Distributed Query by Using ADSI
http://support.microsoft.com/?id=299410
-Sue
On Fri, 11 Feb 2005 09:53:03 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>Im using the 'xp_cmdshell' to know all users that belong to the local
>administrators group and i'm using the 'net use localgroup' to get the outp
ut
>into my report.
>Here is the code and the Output:
>set nocount on
>exec xp_cmdshell 'net use localgroup'
>go
>set nocount off
>OUTPUT:
>output
>
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>Members
>NULL
>--
> Administrator
> DOMAINXXX\Domain Admins
> DOMAINXXX\usera
> DOMAINXXX\userb
>The command completed successfully.
>NULL
>NULL
>Is there any way to avoid lines where appear:
>output
>--
>Alias name administrators
>Comment Administrators have..
>NULL
>and all the information that is not part of the group membership?
>Do you know other way to get this information?
>Thanks,
>Best regards
>

Get one row from each group of rows

Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.On 22 Jul, 05:19, deluca.vice...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1
>
id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2
>
For these, I will take the rows with id=2 and id=7.
>
Thank you, and sory for my english.

The following assumes that there is only one row where column2 = 2 and
column1 is the largest value - as would be the case if (idRef,
column1, column2) was a key for example. If you include DDL with keys
in future posts then people who respond won't have to guess which
columns are unique.

SELECT id, idRef, column1, column2
FROM tbl AS t1
WHERE column2 = 2
AND column1 =
(SELECT MAX(column1)
FROM tbl
WHERE idRef = t1.idRef
AND column2 = 2);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||On Jul 22, 9:19 am, deluca.vice...@.gmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1
>
id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2
>
For these, I will take the rows with id=2 and id=7.
>
Thank you, and sory for my english.

select a.* from tbl a
join
(select idref,max(column1) as column1
from tbl
where column2 = 2
group by idref) as b
on a.idref = b.idref
and a.column1 = b.column1