Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Friday, March 23, 2012

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>sql

Wednesday, March 21, 2012

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in Advance
Hi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegr oups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

I want to find first,second,third,fourth highest from a column of a table

I have tried using max ,Group By and TOP for finding
first,second,third,fourth highest from a column of a table
PLs help me
Thanks in AdvanceHi
create table abhi (
col1 char(1),
col2 int
)
insert into abhi values('A',10)
insert into abhi values('A',8)
insert into abhi values('A',7)
insert into abhi values('A',5)
insert into abhi values('A',4)
insert into abhi values('B',12)
insert into abhi values('B',8)
insert into abhi values('B',6)
insert into abhi values('B',4)
insert into abhi values('B',5)
insert into abhi values('B',3)
DECLARE @.myrow INT
SET @.myrow=2 --change it for your needs
SELECT * FROM abhi a
WHERE (
SELECT COUNT(*) FROM abhi
WHERE col1 = a.col1
AND col2 > a.col2
) <@.myrow
ORDER BY col1, col2 DESC
"ABHI" <abhijitbavdhankar@.gmail.com> wrote in message
news:1129807557.453049.121480@.g14g2000cwa.googlegroups.com...
>I have tried using max ,Group By and TOP for finding
> first,second,third,fourth highest from a column of a table
> PLs help me
> Thanks in Advance
>

Monday, March 12, 2012

I think I found SQL 2005 bug by doing simple Group BY!

I did:

select city,count(*)from classifieds_addressgroupby city

You would think it would distiguish between"Koh Phangan" and "Koh Phangan ". No!!! It shows them as one row in SQL!!! Is this SQL bug?

Aberdeen 1
bayombong 1
Brooklyn 4
Castle Peak Bay 1
Causeway Bay 2
Chiang Mai 1
Hong Kong 1
Hong Kong Wan Chai 1
Hong Kong Island 10
Hong Kong Sheung Wan 1
Karachi 1
Koh Phangan 21
Koh Samui 37
Koh Tao 9
Kowloon 13
Kowloon, Tsim Sha Tsui 1
Lantau Island 1
Mid-Levels 3
New Territories 2
New York 3
North Point 1
Oligar 1
Tsim Sha Tsui 1
Wan Chai 5
Yau Ma Tei 1
Yau Ma Tei, Kowloon 1

Actually I think it is worse than that! I run several queries and they all return 21 rows!!

select*from classifieds_addresswhere city='Koh Phangan'

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

select*from classifieds_addresswhere city='Koh Phangan '

Is not that a bug?? I am using SQL 2005 and the latest service pack!

|||

Seems MS is well aware of this problem:http://support.microsoft.com/kb/316626

I wonder why they do not fix this.

How do I select UNIQUE values (if there are spaces it is different than the one without spaces) and count of those records??

Of now is to late to do SET ANSI_PADDING OFF, since the values are already inserted with trailing spaces top the table...How do I distinguish those records??

|||

So for now you can do:

GROUP BY LTRIM(RTRIM(city))

|||

Jack,

That is not what I look for, I just want the oposite what you are proposing. What you are proposing is already done in group by inside of SQL Server...

I want to find the records where there are different. I want to find UNIQUE records in the table.I want to find those records with trailing spaces and mispells... I thought that Group By would do it, but it treats records with trailing spaces the same as the ones without...

GROUP BY LTRIM(RTRIM(city)) will just do the same as group by Rtrim(city)!

|||

Sorry for misunderstood your problem. Try the code below:

SELECT REPLACE(REPLACE(city,' ','_'),'_',' ')AS city,COUNT(*)FROM dbo.classifieds_addressGROUP BY REPLACE(city,' ','_')

Wednesday, March 7, 2012

I need to display the name of an expanded group

Hi everyone,
I want to display the name of an expanded group within a seperate text
box. The problem is that it doesn't work because my seperate text box
is "not part of the group area" which the name/field belongs to. What
should I do?
How can I access report items of a group outside that group? I though
I could temporarily copy values from a field of a group into a 'global
parameter' (if it's possible, of course) and then make my text box
display the value by referencing that parameter. But since I don't
know how to do that either, I have run out of ideas.On Mar 14, 11:01 am, "mailfo...@.googlemail.com"
<mailfo...@.googlemail.com> wrote:
> Hi everyone,
> I want to display the name of an expanded group within a seperate text
> box. The problem is that it doesn't work because my seperate text box
> is "not part of the group area" which the name/field belongs to. What
> should I do?
> How can I access report items of a group outside that group? I though
> I could temporarily copy values from a field of a group into a 'global
> parameter' (if it's possible, of course) and then make my text box
> display the value by referencing that parameter. But since I don't
> know how to do that either, I have run out of ideas.
If I'm understanding you correctly, you could use something like the
following:
To access a parameter, use an expression like: =Parameters!
ParameterName.Value
To access a dataset outside of it's normal scope, use an expression
like: =Max(Fields!FieldName.Value, "DataSetName")
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Its not possible to have a report item placed outside the group. It can be
placed only on header, footer or in the grouping. One option is that in your
grouping you can insert as many blank rows and then place the report items,
but that too static headers not the details of any rows. ie you can refer one
value and not multiple.
Amarnath,MCTS
"mailforpr@.googlemail.com" wrote:
> Hi everyone,
> I want to display the name of an expanded group within a seperate text
> box. The problem is that it doesn't work because my seperate text box
> is "not part of the group area" which the name/field belongs to. What
> should I do?
> How can I access report items of a group outside that group? I though
> I could temporarily copy values from a field of a group into a 'global
> parameter' (if it's possible, of course) and then make my text box
> display the value by referencing that parameter. But since I don't
> know how to do that either, I have run out of ideas.
>|||Thank you very much, guys.
Is there any other way to determine whether a group has been expanded
or not? I don't necessarily have to display a value of a field of a
specific group area in a seperate text box, but rather something like
"oh, you have just expanded group xyz, so I let you know that in that
text box you see over there"...?
The code might probably look like this:
textbox.Value:
=IIF(groupXYZ.Expanded,"Group XYZ is expanded","nothing or something
else is expanded")
Is that possible?

Friday, February 24, 2012

I need Help.

Hi. I'm trying a SQL like this
SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
its returns me
PRICE
100
150
30
But i lik SQL like this
SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
i want return like
PRICE
280
When i runs second SQL an error happens.
" Cannot perform an aggregate function on an expression containing an
aggregate or a subquery. "
_____________________________________________________________
Murat FÝDANOne way
SELECT SUM(MinPrice)AS SumPrice
FROM (SELECT MIN(PRICE) AS MinPrice
FROM TABLE1
WHERE ID=3D112
AND STATUS=3D1
GROUP BY ID )x
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Aug 23, 9:04 am, "Murat Fidan" <murat.fi...@.tekdurak.com> wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=3D112 AND STATUS=3D1 GROUP BY= ID
> i want return like
> PRICE
> 280
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
> _____________________________________________________________
> Murat F=DDDAN|||Murat Fidan,
Use a derived table.
select sum(min_price)
from
(
SELECT MIN(PRICE) as min_price
FROM TABLE1
WHERE ID=112 AND STATUS=1
GROUP BY ID
) as t
go
AMB
"Murat Fidan" wrote:
> Hi. I'm trying a SQL like this
> SELECT MIN(PRICE) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> its returns me
> PRICE
> 100
> 150
> 30
> But i lik SQL like this
> SELECT SUM(MIN(PRICE)) FROM TABLE1 WHERE ID=112 AND STATUS=1 GROUP BY ID
> i want return like
> PRICE
> 280
>
> When i runs second SQL an error happens.
> " Cannot perform an aggregate function on an expression containing an
> aggregate or a subquery. "
>
> _____________________________________________________________
> Murat FÃ?DAN
>
>

Sunday, February 19, 2012

I need help with a Query and Count

I have a pretty simple query:

Select title, avg(score) from votes group by title order by avg(score) Desc

This returns a result like this:

Abbey Road 4.0
The White Album 3.5
Meet the Beatles 3.0

The values in the score field are always 1, 2, 3, 4, or 5

What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this.

Abbey Road 4.0 0 0 1 1 1
The White Album 3.5 0 1 0 0 1
Meet the Beatles 3.0 2 2 2 2 2

I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets.

Any suggestions would be greatly appreciated,

Chris

I found a solution using sum and case.

Chris