Is it possible to group data in charts?
e.g. I need to return a graph with % and other data per office separately
for a board pack so the charts either appear per office on a page or a couple
of charts per page.You can create a TABLE and put a chart in a cell in the table and add a
grouping to the table.
"Changing Dates in reports & parameters"
<ChangingDatesinreportsparameters@.discussions.microsoft.com> wrote in
message news:657FA2D2-CED1-4B67-ABB1-8E1D6141DEBC@.microsoft.com...
> Is it possible to group data in charts?
> e.g. I need to return a graph with % and other data per office separately
> for a board pack so the charts either appear per office on a page or a
> couple
> of charts per page.|||Hi Dean,
Worked a treat, thanks for you help.
Lisa
"Dean" wrote:
> You can create a TABLE and put a chart in a cell in the table and add a
> grouping to the table.
> "Changing Dates in reports & parameters"
> <ChangingDatesinreportsparameters@.discussions.microsoft.com> wrote in
> message news:657FA2D2-CED1-4B67-ABB1-8E1D6141DEBC@.microsoft.com...
> > Is it possible to group data in charts?
> > e.g. I need to return a graph with % and other data per office separately
> > for a board pack so the charts either appear per office on a page or a
> > couple
> > of charts per page.
>
>
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Monday, March 26, 2012
Grouping Data Based On Return From Stored Procedure
I'm having some difficulty getting the appropriate results for my scenerio. I have two different datasets that I'm using. One is consisting of two joined tables and the other consisting of one sp. The sp's parameters rely on two things- one is the companyNum (inputed when the user runs the report) and two is the ContactNumType. The ContactTypeNum comes from the dataset of tables. I need to have a table consisting of this format:
ContactNumType1 (From the Tables)
File_Name1 (From the sp)
File_Name4 (From the sp)
File_Name3 (From the sp)
ContactNumType2 (From the Tables)
File_Name2 (From the sp)
File_Name7(From the sp)
ContactNumType3 (From the Tables)
File_Name5 (From the sp)
ContactNumType4 (From the Tables)
File_Name6 (From the sp)
File_Name10 (From the sp)
File_Name8(From the sp)
File_Name9 (From the sp)
So essentially what is going on is that every returned File_Name is grouped based upon the type of ContactNumType. My table returns the appropriate ContactNumTypes and the appropriate number of File_Names but returns only the first File_Name for each row. The File_Names should only grouped by the ContactTypeNums and each be unique. Is there any way to do that?
-
Edited: I still am trying to work this out. I've tried a few run-arounds but none have worked. Adding custom code apparently is too risky at this point because of the security precautions that I've been instructed to take. Any help would be greatly appreciated as this project has been going on for days now....
Grouping by hour, day, month, etc
I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Monday, March 12, 2012
GROUP BY/ HAVING CLAUSE problem
I'm trying to set up my adhoc query to return just one single record, which is aliased as 'foreign' in my sql statement (which is just the total amount of foreign overseas orders for just one day. All Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status = 1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING clause, it will return three records, with one being blank.
?
.netsports
http://www.akadia.com/services/dealing_with_null_values.html|||i am using four table(ForumMain,ForumThreads,ReplyToThread,Authentication) in my forum.I have 4 asp.net pages in this forum. On the very first page, I am showing the Main category of forums.i.e all forums,last thread posted,total threads so far and the total number of replies to each thead and of course the name of the user who generated or added last thread.
To do this, i am using count function to count the total replies to each thread,RepliesToThread table is doing that(not counting total threads yet),Forum Category field from the ForumMain table,ThreadName from the ForumThreads table and the username from the Authentication table.
I am using Group By clause as well but every time a new thread is added from AddThread.aspx page, the name of the main category which the new thread is added into, is repeated on the main page.
i.e. if I add a new thread in main category DATABASE, and this main category has already one thread, the main page show me like
DATABASE already existing category.
date:25/09/2005
DATABASE new category
date:26/09/2005
rather than it should show me
DATABASE new category
date:26/09/2005
What should I do to avoid this repetition?
Thanks in advance.|||Try the link below and see if GROUP BY with CUBE or ROLLUP operator will help with you problem and some restrictions apply. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp|||Caddre, this linke you providehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp is not providing help to solve my problem.
I am looking forward to more helpful replies from you or anybody else.|||Hi,
You're summingSale_Type_Id values from Orders table. I thinks this is not what you want to get. You can Count theSale_Type_Id values to have the number of orders.
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id ELSE NULL END) AS [foreign]
And if your records in Orders table have Sale_Type_Id values greaterthan 2, for each distinct value of Sale_Type_Id you'll get a differentrow.
Because you group your records due to Sale_Type_Id's. Note that if itis 2 or less. You group them as nulls. And remove only the null groupby using the Having clause.
So you still have groups having Sale_Type_Id's greater than 2
I hope it is helpfull
Eralper
http://www.eralper.com
Friday, March 9, 2012
Group By Question
I am writing a very simple polling application and I am trying to return the
results in which it will count the number of responses for a given survey.
What I wrote was:
SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
GROUP BY SurveyChoice.Choice;
This is good because it returns Choices even if they were not used (i.e.,
nobody voted for a particular option and the Quantity is returned as 0).
But if I add the following, I lose all of the Quantity=0 records.
HAVING SurveyVote.FK_SurveyID=1
Is there a way to have both?
DaveMaybe
HAVING (SurveyVote.FK_SurveyID=1 OR COUNT(SurveyVote.FK_SurveyChoiceID)=0)
http://www.aspfaq.com/
(Reverse address to reply.)
"David Mroz" <dave@.glimmernet.com> wrote in message
news:OWUlBefGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> I am writing a very simple polling application and I am trying to return
the
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS
Quantity
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>|||David,
I am a little bit
. The column FK_SurveyID is not in the select
list, neither in the group by clause, I wonder why sql server is not giving
an error?
If you want to filter the rows using WHERE clause, then you can use "GROUP
BY ALL SurveyChoice.Choice" to see all groups even the ones with no row that
meet the filter.
SELECT
SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM
SurveyVote
RIGHT JOIN
SurveyChoice
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
where
SurveyVote.FK_SurveyID = 1
GROUP BY
SurveyChoice.Choice;
AMB
"David Mroz" wrote:
> I am writing a very simple polling application and I am trying to return t
he
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantit
y
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>
>|||On Wed, 23 Feb 2005 17:31:57 -0500, David Mroz wrote:
>I am writing a very simple polling application and I am trying to return th
e
>results in which it will count the number of responses for a given survey.
>What I wrote was:
>SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
>FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
>SurveyChoice.PK_SurveyChoiceID
>GROUP BY SurveyChoice.Choice;
>This is good because it returns Choices even if they were not used (i.e.,
>nobody voted for a particular option and the Quantity is returned as 0).
>But if I add the following, I lose all of the Quantity=0 records.
>HAVING SurveyVote.FK_SurveyID=1
>Is there a way to have both?
Hi Dave,
In addition to the answers by Aaron and Alejandro, I *think* the
following will work as well. They are untested, though, since you didn't
provide CREATE TABLE and INSERT statements to create a test set.
1. Using LEFT JOIN instead of RIGHT JOIN
SELECT SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyChoice
LEFT JOIN SurveyVote
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1
GROUP BY SurveyChoice.Choice
2. Using subselect instead of join
SELECT SurveyChoice.Choice,
(SELECT Count(SurveyVote.FK_SurveyChoiceID)
FROM SurveyVote
WHERE SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1) AS Quantity
FROM SurveyChoice
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
results in which it will count the number of responses for a given survey.
What I wrote was:
SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
GROUP BY SurveyChoice.Choice;
This is good because it returns Choices even if they were not used (i.e.,
nobody voted for a particular option and the Quantity is returned as 0).
But if I add the following, I lose all of the Quantity=0 records.
HAVING SurveyVote.FK_SurveyID=1
Is there a way to have both?
DaveMaybe
HAVING (SurveyVote.FK_SurveyID=1 OR COUNT(SurveyVote.FK_SurveyChoiceID)=0)
http://www.aspfaq.com/
(Reverse address to reply.)
"David Mroz" <dave@.glimmernet.com> wrote in message
news:OWUlBefGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> I am writing a very simple polling application and I am trying to return
the
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS
Quantity
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>|||David,
I am a little bit
. The column FK_SurveyID is not in the selectlist, neither in the group by clause, I wonder why sql server is not giving
an error?
If you want to filter the rows using WHERE clause, then you can use "GROUP
BY ALL SurveyChoice.Choice" to see all groups even the ones with no row that
meet the filter.
SELECT
SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM
SurveyVote
RIGHT JOIN
SurveyChoice
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
where
SurveyVote.FK_SurveyID = 1
GROUP BY
SurveyChoice.Choice;
AMB
"David Mroz" wrote:
> I am writing a very simple polling application and I am trying to return t
he
> results in which it will count the number of responses for a given survey.
> What I wrote was:
> SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantit
y
> FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
> SurveyChoice.PK_SurveyChoiceID
> GROUP BY SurveyChoice.Choice;
> This is good because it returns Choices even if they were not used (i.e.,
> nobody voted for a particular option and the Quantity is returned as 0).
> But if I add the following, I lose all of the Quantity=0 records.
> HAVING SurveyVote.FK_SurveyID=1
> Is there a way to have both?
> Dave
>
>
>|||On Wed, 23 Feb 2005 17:31:57 -0500, David Mroz wrote:
>I am writing a very simple polling application and I am trying to return th
e
>results in which it will count the number of responses for a given survey.
>What I wrote was:
>SELECT SurveyChoice.Choice, Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
>FROM SurveyVote RIGHT JOIN SurveyChoice ON SurveyVote.FK_SurveyChoiceID =
>SurveyChoice.PK_SurveyChoiceID
>GROUP BY SurveyChoice.Choice;
>This is good because it returns Choices even if they were not used (i.e.,
>nobody voted for a particular option and the Quantity is returned as 0).
>But if I add the following, I lose all of the Quantity=0 records.
>HAVING SurveyVote.FK_SurveyID=1
>Is there a way to have both?
Hi Dave,
In addition to the answers by Aaron and Alejandro, I *think* the
following will work as well. They are untested, though, since you didn't
provide CREATE TABLE and INSERT statements to create a test set.
1. Using LEFT JOIN instead of RIGHT JOIN
SELECT SurveyChoice.Choice,
Count(SurveyVote.FK_SurveyChoiceID) AS Quantity
FROM SurveyChoice
LEFT JOIN SurveyVote
ON SurveyVote.FK_SurveyChoiceID = SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1
GROUP BY SurveyChoice.Choice
2. Using subselect instead of join
SELECT SurveyChoice.Choice,
(SELECT Count(SurveyVote.FK_SurveyChoiceID)
FROM SurveyVote
WHERE SurveyVote.FK_SurveyChoiceID =
SurveyChoice.PK_SurveyChoiceID
AND SurveyVote.FK_SurveyID = 1) AS Quantity
FROM SurveyChoice
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
group by qst
In the following query I want to return as a last column the aggregate max
value of the OrderInList field.
input >>
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by *
order by OrderInList
output >>
Column 'tbl_activities.Activity_ID' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.
Can I achieve what I want by including the extra MaxOrderInList column
instead of returning an output param?
TIAalto wrote:
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
it
> is not contained in either an aggregate function or the GROUP BY clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>
Don't be lazy, specify your field list and stop using SELECT *. Any
non-aggregate field that is listed in your field list must also be
included in your GROUP BY clause, like this:
SELECT col1, col2, col3, MAC(col4)
FROM table
GROUP BY col1, col2, col3
It's generally considered bad practice to use "SELECT *".|||You can't GROUP BY *, and you shouldn't use SELECT * in production code
anyway.
It's really not that hard to generate a list of the columns in the table,
however what do you expect to happen here? If you have data like this:
ActivityID OrderInList
1 1
1 2
What is your desired output?
If you group by ALL columns, then your max(OrderInList) is meaningless.
Try it:
CREATE TABLE dbo.floob
(
ActivityID INT,
OrderInList INT
);
SET NOCOUNT ON;
INSERT dbo.floob(ActivityID,OrderInList)
SELECT 1,1
UNION ALL
SELECT 1,2;
SELECT
ActivityID, OrderInList,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID, OrderInList;
-- maybe what you meant was:
SELECT
ActivityID,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID;
GO
DROP TABLE dbo.floob;
GO
Without proper DDL, sample data and desired results, I have little else to
offer, except that grouping by all columns in the table doesn't make any
sense.
http://www.aspfaq.com/5006
"alto" <altodorov@.hotmail.com> wrote in message
news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>|||Last column is to return the same value for all records, like this
ActivityID, other cols..., MaxOrderInList
1 1,... 2
1 2,... 2
And BTW, in response to Tracy I'd already tried w/ all the fields in the
goup by - it wouldn't allow me to include an aggregate column in the list.
Again I could return the max OrderInList in an output param; I'm just asking
if the alternative above is possible.
Txs!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uX8gRLfmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> You can't GROUP BY *, and you shouldn't use SELECT * in production code
> anyway.
> It's really not that hard to generate a list of the columns in the table,
> however what do you expect to happen here? If you have data like this:
> ActivityID OrderInList
> 1 1
> 1 2
> What is your desired output?
> If you group by ALL columns, then your max(OrderInList) is meaningless.
> Try it:
> CREATE TABLE dbo.floob
> (
> ActivityID INT,
> OrderInList INT
> );
> SET NOCOUNT ON;
> INSERT dbo.floob(ActivityID,OrderInList)
> SELECT 1,1
> UNION ALL
> SELECT 1,2;
> SELECT
> ActivityID, OrderInList,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID, OrderInList;
> -- maybe what you meant was:
> SELECT
> ActivityID,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID;
> GO
> DROP TABLE dbo.floob;
> GO
>
> Without proper DDL, sample data and desired results, I have little else to
> offer, except that grouping by all columns in the table doesn't make any
> sense.
> http://www.aspfaq.com/5006
>
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
>|||"alto" <altodorov@.hotmail.com> wrote in message
news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> Last column is to return the same value for all records, like this
> ActivityID, other cols..., MaxOrderInList
> 1 1,... 2
> 1 2,... 2
> And BTW, in response to Tracy I'd already tried w/ all the fields in the
> goup by - it wouldn't allow me to include an aggregate column in the list.
What doesn't "wouldn't allow me" mean? Did you get an error message? What
was it? What code did you try?
I think you would have to do this with a correlated subquery, not group by.
For example, using the DDL and sample data I provided earlier:
SELECT
ActivityID,
OrderInList,
MaxOrderInList = (
SELECT MAX(OrderInList)
FROM dbo.floob
WHERE ActivityID = f.ActivityID
)
FROM
dbo.floob f;
A|||>> Last column is to return the same value for all records [sic], like this ..<<
Try a scalar subquery expression
SELECT a, b, c, ...
(SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
FROM Foobar;|||I mean I tried this query
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
max(OrderInList)
order by OrderInList
w/ this result:
Server: Msg 144, Level 15, State 1, Line 26
Cannot use an aggregate or a subquery in an expression used for the group by
list of a GROUP BY clause.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJsIHWfmGHA.856@.TK2MSFTNGP03.phx.gbl...
> "alto" <altodorov@.hotmail.com> wrote in message
> news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> What doesn't "wouldn't allow me" mean? Did you get an error message?
> What was it? What code did you try?
> I think you would have to do this with a correlated subquery, not group
> by. For example, using the DDL and sample data I provided earlier:
> SELECT
> ActivityID,
> OrderInList,
> MaxOrderInList = (
> SELECT MAX(OrderInList)
> FROM dbo.floob
> WHERE ActivityID = f.ActivityID
> )
> FROM
> dbo.floob f;
>
> A
>|||Bingo, it worked:
input >>
select Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated,
(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007') as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
--(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007')
OrderInList
order by OrderInList
output (see last column returned) >>
1309 81 1 NULL activite 1 emplacement 1 NULL NULL NULL NULL 2006-2007 1
1 2006-06-19 00:00:00.000 2006-06-24 00:00:00.000 2006-06-27 09:24:57.007 2
1310 81 1 NULL activite 2 emplacement 2 NULL NULL NULL NULL 2006-2007 1
4 2006-06-05 00:00:00.000 2006-06-17 00:00:00.000 2006-06-27 09:25:21.550 2
Txs!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151418422.623163.134150@.x69g2000cwx.googlegroups.com...
> Try a scalar subquery expression
> SELECT a, b, c, ...
> (SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
> FROM Foobar;
>|||>I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
And like I said before, selecting all columns and then aggregating one of
them doesn't make sense.
A|||alto wrote:
> I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by Activity_ID,
> Center_ID,
> Category_ID,
> Type_of_Activity ,
> [Description] ,
> Location_Activity ,
> Date_Activity ,
> Total_number_of_attendies ,
> Purpose_activity ,
> Fully_organized_by_org ,
> Partial_assistance_of_org ,
> BeginingDate_FY ,
> Fiscal_Year ,
> memo ,
> IsRequest ,
> ProposedGuest ,
> ActivityType ,
> DateStart ,
> DateEnd ,
> DateCreated ,
> max(OrderInList)
> order by OrderInList
> w/ this result:
> Server: Msg 144, Level 15, State 1, Line 26
> Cannot use an aggregate or a subquery in an expression used for the group
by
> list of a GROUP BY clause.
>
**THINK** about what you're doing here. Your aggregate value (MAX) is
calculated by grouping together like values from the table. The value
of MAX is unknown until that grouping has been done, so HOW can you
include it in the grouping criteria?
As I said before, STOP USING SELECT *, be specific about the fields that
you want returned by the SELECT, and include those fields (excluding the
aggregate) in your GROUP BY.
value of the OrderInList field.
input >>
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by *
order by OrderInList
output >>
Column 'tbl_activities.Activity_ID' is invalid in the select list because it
is not contained in either an aggregate function or the GROUP BY clause.
Can I achieve what I want by including the extra MaxOrderInList column
instead of returning an output param?
TIAalto wrote:
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
it
> is not contained in either an aggregate function or the GROUP BY clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>
Don't be lazy, specify your field list and stop using SELECT *. Any
non-aggregate field that is listed in your field list must also be
included in your GROUP BY clause, like this:
SELECT col1, col2, col3, MAC(col4)
FROM table
GROUP BY col1, col2, col3
It's generally considered bad practice to use "SELECT *".|||You can't GROUP BY *, and you shouldn't use SELECT * in production code
anyway.
It's really not that hard to generate a list of the columns in the table,
however what do you expect to happen here? If you have data like this:
ActivityID OrderInList
1 1
1 2
What is your desired output?
If you group by ALL columns, then your max(OrderInList) is meaningless.
Try it:
CREATE TABLE dbo.floob
(
ActivityID INT,
OrderInList INT
);
SET NOCOUNT ON;
INSERT dbo.floob(ActivityID,OrderInList)
SELECT 1,1
UNION ALL
SELECT 1,2;
SELECT
ActivityID, OrderInList,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID, OrderInList;
-- maybe what you meant was:
SELECT
ActivityID,
MAXOrderInList = MAX(OrderInList)
FROM
dbo.floob
GROUP BY
ActivityID;
GO
DROP TABLE dbo.floob;
GO
Without proper DDL, sample data and desired results, I have little else to
offer, except that grouping by all columns in the table doesn't make any
sense.
http://www.aspfaq.com/5006
"alto" <altodorov@.hotmail.com> wrote in message
news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
> In the following query I want to return as a last column the aggregate max
> value of the OrderInList field.
> input >>
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by *
> order by OrderInList
> output >>
> Column 'tbl_activities.Activity_ID' is invalid in the select list because
> it is not contained in either an aggregate function or the GROUP BY
> clause.
> Can I achieve what I want by including the extra MaxOrderInList column
> instead of returning an output param?
> TIA
>|||Last column is to return the same value for all records, like this
ActivityID, other cols..., MaxOrderInList
1 1,... 2
1 2,... 2
And BTW, in response to Tracy I'd already tried w/ all the fields in the
goup by - it wouldn't allow me to include an aggregate column in the list.
Again I could return the max OrderInList in an output param; I'm just asking
if the alternative above is possible.
Txs!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uX8gRLfmGHA.4816@.TK2MSFTNGP03.phx.gbl...
> You can't GROUP BY *, and you shouldn't use SELECT * in production code
> anyway.
> It's really not that hard to generate a list of the columns in the table,
> however what do you expect to happen here? If you have data like this:
> ActivityID OrderInList
> 1 1
> 1 2
> What is your desired output?
> If you group by ALL columns, then your max(OrderInList) is meaningless.
> Try it:
> CREATE TABLE dbo.floob
> (
> ActivityID INT,
> OrderInList INT
> );
> SET NOCOUNT ON;
> INSERT dbo.floob(ActivityID,OrderInList)
> SELECT 1,1
> UNION ALL
> SELECT 1,2;
> SELECT
> ActivityID, OrderInList,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID, OrderInList;
> -- maybe what you meant was:
> SELECT
> ActivityID,
> MAXOrderInList = MAX(OrderInList)
> FROM
> dbo.floob
> GROUP BY
> ActivityID;
> GO
> DROP TABLE dbo.floob;
> GO
>
> Without proper DDL, sample data and desired results, I have little else to
> offer, except that grouping by all columns in the table doesn't make any
> sense.
> http://www.aspfaq.com/5006
>
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23VHcK%23emGHA.2204@.TK2MSFTNGP03.phx.gbl...
>|||"alto" <altodorov@.hotmail.com> wrote in message
news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> Last column is to return the same value for all records, like this
> ActivityID, other cols..., MaxOrderInList
> 1 1,... 2
> 1 2,... 2
> And BTW, in response to Tracy I'd already tried w/ all the fields in the
> goup by - it wouldn't allow me to include an aggregate column in the list.
What doesn't "wouldn't allow me" mean? Did you get an error message? What
was it? What code did you try?
I think you would have to do this with a correlated subquery, not group by.
For example, using the DDL and sample data I provided earlier:
SELECT
ActivityID,
OrderInList,
MaxOrderInList = (
SELECT MAX(OrderInList)
FROM dbo.floob
WHERE ActivityID = f.ActivityID
)
FROM
dbo.floob f;
A|||>> Last column is to return the same value for all records [sic], like this ..<<
Try a scalar subquery expression
SELECT a, b, c, ...
(SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
FROM Foobar;|||I mean I tried this query
select *, max(OrderInList) as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
max(OrderInList)
order by OrderInList
w/ this result:
Server: Msg 144, Level 15, State 1, Line 26
Cannot use an aggregate or a subquery in an expression used for the group by
list of a GROUP BY clause.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJsIHWfmGHA.856@.TK2MSFTNGP03.phx.gbl...
> "alto" <altodorov@.hotmail.com> wrote in message
> news:OT2ZkSfmGHA.4868@.TK2MSFTNGP04.phx.gbl...
> What doesn't "wouldn't allow me" mean? Did you get an error message?
> What was it? What code did you try?
> I think you would have to do this with a correlated subquery, not group
> by. For example, using the DDL and sample data I provided earlier:
> SELECT
> ActivityID,
> OrderInList,
> MaxOrderInList = (
> SELECT MAX(OrderInList)
> FROM dbo.floob
> WHERE ActivityID = f.ActivityID
> )
> FROM
> dbo.floob f;
>
> A
>|||Bingo, it worked:
input >>
select Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated,
(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007') as MaxOrderInList
from tbl_activities
where Center_ID = 81
and Fiscal_Year = '2006-2007'
group by Activity_ID,
Center_ID,
Category_ID,
Type_of_Activity ,
[Description] ,
Location_Activity ,
Date_Activity ,
Total_number_of_attendies ,
Purpose_activity ,
Fully_organized_by_org ,
Partial_assistance_of_org ,
BeginingDate_FY ,
Fiscal_Year ,
memo ,
IsRequest ,
ProposedGuest ,
ActivityType ,
DateStart ,
DateEnd ,
DateCreated ,
--(select max(OrderInList) from tbl_activities where Center_ID = 81 and
Fiscal_Year = '2006-2007')
OrderInList
order by OrderInList
output (see last column returned) >>
1309 81 1 NULL activite 1 emplacement 1 NULL NULL NULL NULL 2006-2007 1
1 2006-06-19 00:00:00.000 2006-06-24 00:00:00.000 2006-06-27 09:24:57.007 2
1310 81 1 NULL activite 2 emplacement 2 NULL NULL NULL NULL 2006-2007 1
4 2006-06-05 00:00:00.000 2006-06-17 00:00:00.000 2006-06-27 09:25:21.550 2
Txs!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151418422.623163.134150@.x69g2000cwx.googlegroups.com...
> Try a scalar subquery expression
> SELECT a, b, c, ...
> (SELECT MAX(order_in_list) FROM Foobar) AS order_in_list_max
> FROM Foobar;
>|||>I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
And like I said before, selecting all columns and then aggregating one of
them doesn't make sense.
A|||alto wrote:
> I mean I tried this query
> select *, max(OrderInList) as MaxOrderInList
> from tbl_activities
> where Center_ID = 81
> and Fiscal_Year = '2006-2007'
> group by Activity_ID,
> Center_ID,
> Category_ID,
> Type_of_Activity ,
> [Description] ,
> Location_Activity ,
> Date_Activity ,
> Total_number_of_attendies ,
> Purpose_activity ,
> Fully_organized_by_org ,
> Partial_assistance_of_org ,
> BeginingDate_FY ,
> Fiscal_Year ,
> memo ,
> IsRequest ,
> ProposedGuest ,
> ActivityType ,
> DateStart ,
> DateEnd ,
> DateCreated ,
> max(OrderInList)
> order by OrderInList
> w/ this result:
> Server: Msg 144, Level 15, State 1, Line 26
> Cannot use an aggregate or a subquery in an expression used for the group
by
> list of a GROUP BY clause.
>
**THINK** about what you're doing here. Your aggregate value (MAX) is
calculated by grouping together like values from the table. The value
of MAX is unknown until that grouping has been done, so HOW can you
include it in the grouping criteria?
As I said before, STOP USING SELECT *, be specific about the fields that
you want returned by the SELECT, and include those fields (excluding the
aggregate) in your GROUP BY.
Wednesday, March 7, 2012
Group by Month
If I have a date column and want to return a columns that sum by each month of the year, what is the best way to do that?
Example
Date Amount
1/3/2007 10
1/7/2007 15
3/4/2007 8
3/21/2007 19
5/33/2007 12
9/6/2007 5
12/8/20007 4
12/12/2007 10
Return:
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Amount 25 0 27 0 12 0 0 0 5 0 0 14
The most convenient way is to use a PIVOT based on the DATEPART of the date using 'MM' -- the month part -- as the target datepart. Another alternative would involve the use of SUM and CASE over 12 different columns again based on a date part.
( My overview sounds like mumbo jumbo to me, too. Can somebody pick me up? )
|||Here are a couple different approaches, some useful for SQL 2000/2005, and some only for SQL 2005.
Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955
Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html
Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/
Group by Max Count ? how to do query?
Can anyone tell me how to do this query please?
3 fields. provider, site, visitdate. Given a date range for
visitdate,
Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).
Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.
THANKS A LOTSome DDL (i.e. create table statements) would be useful.
Thomas
<bringmewater@.gmail.com> wrote in message
news:1114630228.267194.170960@.g14g2000cwa.googlegroups.com...
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>|||Try,
create view v1
as
select
provider,
site,
count(*) as number_of_visit
from
t1
where
visitdate >= convert(char(8), @.sd, 112) and visitdate < dateadd(day, 1,
convert(char(8), @.ed, 112))
group by
provider,
site
having
count(*) > 0
go
select
provider,
site,
number_of_visit
from
v1 as a
where
number_of_visit = (select max(b.number_of_visit) from v1 as b where
b.provider = a.provider)
go
AMB
"bringmewater@.gmail.com" wrote:
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>
3 fields. provider, site, visitdate. Given a date range for
visitdate,
Return all providers that have at least one visit in that date range
and also display which site has the most visitdates for EACH provider
for all records (not date constrained).
Since a provider can have visits at multiple sites I only want to
return the site where they have the most visits.
THANKS A LOTSome DDL (i.e. create table statements) would be useful.
Thomas
<bringmewater@.gmail.com> wrote in message
news:1114630228.267194.170960@.g14g2000cwa.googlegroups.com...
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>|||Try,
create view v1
as
select
provider,
site,
count(*) as number_of_visit
from
t1
where
visitdate >= convert(char(8), @.sd, 112) and visitdate < dateadd(day, 1,
convert(char(8), @.ed, 112))
group by
provider,
site
having
count(*) > 0
go
select
provider,
site,
number_of_visit
from
v1 as a
where
number_of_visit = (select max(b.number_of_visit) from v1 as b where
b.provider = a.provider)
go
AMB
"bringmewater@.gmail.com" wrote:
> Can anyone tell me how to do this query please?
> 3 fields. provider, site, visitdate. Given a date range for
> visitdate,
> Return all providers that have at least one visit in that date range
> and also display which site has the most visitdates for EACH provider
> for all records (not date constrained).
> Since a provider can have visits at multiple sites I only want to
> return the site where they have the most visits.
> THANKS A LOT
>
Sunday, February 26, 2012
group by and non-aggregate columns
How do I return non-aggregate, non-group columns in a group by select? For
example:
select count(*) numstores, b.reshipcd from branches b join reship r on
b.reshipcd = r.reshipcd group by b.reshipcd
works fine but
select count(*) numstores, r.addr, b.reshipcd from branches b join reship r
on b.reshipcd = r.reshipcd group by b.reshipcd
fails since r.addr is neither in the group by clause nor an aggregate
column. Yet I need the address in the select. How can this be done?
Thanks for any help.
Bernie YaegerAnything that's non-aggregate needs to be in the GROUP BY clause.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> How do I return non-aggregate, non-group columns in a group by select?
For
> example:
> select count(*) numstores, b.reshipcd from branches b join reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> works fine but
> select count(*) numstores, r.addr, b.reshipcd from branches b join reship
r
> on b.reshipcd = r.reshipcd group by b.reshipcd
> fails since r.addr is neither in the group by clause nor an aggregate
> column. Yet I need the address in the select. How can this be done?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Adam,
Tx for the response - of course, that's only logical.
Thanks again,
Bernie
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:u6DELrVrDHA.2520@.TK2MSFTNGP09.phx.gbl...
> Anything that's non-aggregate needs to be in the GROUP BY clause.
>
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> > How do I return non-aggregate, non-group columns in a group by select?
> For
> > example:
> >
> > select count(*) numstores, b.reshipcd from branches b join reship r on
> > b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > works fine but
> >
> > select count(*) numstores, r.addr, b.reshipcd from branches b join
reship
> r
> > on b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > fails since r.addr is neither in the group by clause nor an aggregate
> > column. Yet I need the address in the select. How can this be done?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>|||You can create a sub-query which will do the Group By part, then join that result to the Outer quer
Select...
From <table> t
( select count(*) numstores, b.reshipcd from branches b join reship r o
b.reshipcd = r.reshipcd group by b.reshipc
) As
Where t.Columnname = g.Columnnam|||Hi Prasanna,
I was thinking about something like this, but it's only logical if the other
columns are all the same, thus making the group by logical if it contains
them all.
Thanks for your response.
Bernie
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:58FB235A-8AFF-4F3A-87A0-AF8A28C5365B@.microsoft.com...
> You can create a sub-query which will do the Group By part, then join that
result to the Outer query
> Select...
> From <table> t,
> ( select count(*) numstores, b.reshipcd from branches b join
reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> ) As g
> Where t.Columnname = g.Columnname
>
example:
select count(*) numstores, b.reshipcd from branches b join reship r on
b.reshipcd = r.reshipcd group by b.reshipcd
works fine but
select count(*) numstores, r.addr, b.reshipcd from branches b join reship r
on b.reshipcd = r.reshipcd group by b.reshipcd
fails since r.addr is neither in the group by clause nor an aggregate
column. Yet I need the address in the select. How can this be done?
Thanks for any help.
Bernie YaegerAnything that's non-aggregate needs to be in the GROUP BY clause.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> How do I return non-aggregate, non-group columns in a group by select?
For
> example:
> select count(*) numstores, b.reshipcd from branches b join reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> works fine but
> select count(*) numstores, r.addr, b.reshipcd from branches b join reship
r
> on b.reshipcd = r.reshipcd group by b.reshipcd
> fails since r.addr is neither in the group by clause nor an aggregate
> column. Yet I need the address in the select. How can this be done?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Adam,
Tx for the response - of course, that's only logical.
Thanks again,
Bernie
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:u6DELrVrDHA.2520@.TK2MSFTNGP09.phx.gbl...
> Anything that's non-aggregate needs to be in the GROUP BY clause.
>
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> > How do I return non-aggregate, non-group columns in a group by select?
> For
> > example:
> >
> > select count(*) numstores, b.reshipcd from branches b join reship r on
> > b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > works fine but
> >
> > select count(*) numstores, r.addr, b.reshipcd from branches b join
reship
> r
> > on b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > fails since r.addr is neither in the group by clause nor an aggregate
> > column. Yet I need the address in the select. How can this be done?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>|||You can create a sub-query which will do the Group By part, then join that result to the Outer quer
Select...
From <table> t
( select count(*) numstores, b.reshipcd from branches b join reship r o
b.reshipcd = r.reshipcd group by b.reshipc
) As
Where t.Columnname = g.Columnnam|||Hi Prasanna,
I was thinking about something like this, but it's only logical if the other
columns are all the same, thus making the group by logical if it contains
them all.
Thanks for your response.
Bernie
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:58FB235A-8AFF-4F3A-87A0-AF8A28C5365B@.microsoft.com...
> You can create a sub-query which will do the Group By part, then join that
result to the Outer query
> Select...
> From <table> t,
> ( select count(*) numstores, b.reshipcd from branches b join
reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> ) As g
> Where t.Columnname = g.Columnname
>
Friday, February 24, 2012
GROUP BY / HAVING clauses problem
I'm trying to set up my adhoc query to return just one single record,
which is aliased as 'foreign' in my sql statement (which is just the
total amount of foreign overseas orders for just one day. All
Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING
clause, it will return three records, with one being blank.
?
.netsportsIf I understand correctly, your GROUP BY is on:
All Orders.Sale_Type_ID greater than 2
NULL
So, without the HAVING I would expect one row returned for each Sale_Type_ID
> 2 and one for all the rest which become NULL. Do you have two ID>2 within
the rows covered by your WHERE clause.
The HAVING apparently is able to prune out the NULL value.
Perhaps all you wanted was:
SELECT SUM(Orders.Sale_Type_Id) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =1) AND
Orders.Sale_Type_ID > 2
RLF
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1127848228.326875.56120@.g49g2000cwa.googlegroups.com...
> I'm trying to set up my adhoc query to return just one single record,
> which is aliased as 'foreign' in my sql statement (which is just the
> total amount of foreign overseas orders for just one day. All
> Sale_Type_Ids over 2 [integer datatype] are foreign orders):
> SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) AS foreign
> FROM Orders INNER JOIN
> Processing ON Orders.ID = Processing.Order_ID
> WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
> 1)
> GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
> ELSE NULL END
> HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) >= 0)
> ..but my resultset is returning two records. If I remove the HAVING
> clause, it will return three records, with one being blank.
> ?
> .netsports
>|||If you only want a single row then remove the GROUP BY clause. GROUP BY
returns one row per group.
David Portas
SQL Server MVP
--|||Thanks. Looks like i'm getting the desired resultset. This sql
statement was sort of a permutation of an extensive one that would
bring back multiple records, but yes, I was starting to think if the
Group By was really necessary.|||I'm inferring your schema to be something like this:
CREATE TABLE Orders
(
ID INT PRIMARY KEY ?
, Sale_Type_Id INT
, Order_Total MONEY ?maybe
)
CREATE TABLE Processing
(
OrderID INT REFERENCES Orders(OrderID)
, Orderdate DATETIME
, Status INT
)
Your question sounds like you're either trying to find a count of
orders, or total amount purchased (which is why I made up that
ordertotal field). If either of those are what you're looking for,
there are much syntactically simpler solutions.
e.g.
SELECT COUNT(*) CountOfForeignOrders
, SUM(Order_Total) TotalOfForeignOrders
FROM Orders
WHERE Sale_Type_Id > 2 AND EXISTS(SELECT * FROM Processing WHERE
OrderID = Orders.ID AND OrderDate = '20050915' AND Status = 1)
When performing aggregates with join clauses, it's possible to
aggregate the same value more than once if a join causes the same row
to appear multiple times in the resultset. GROUP BY and HAVING aren't
necessary unless you're selecting your data based on your aggregate,
and if you'd return multiple aggregate sets.
-Alan
which is aliased as 'foreign' in my sql statement (which is just the
total amount of foreign overseas orders for just one day. All
Sale_Type_Ids over 2 [integer datatype] are foreign orders):
SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
1)
GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
ELSE NULL END
HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
Orders.Sale_Type_Id ELSE NULL END) >= 0)
..but my resultset is returning two records. If I remove the HAVING
clause, it will return three records, with one being blank.
?
.netsportsIf I understand correctly, your GROUP BY is on:
All Orders.Sale_Type_ID greater than 2
NULL
So, without the HAVING I would expect one row returned for each Sale_Type_ID
> 2 and one for all the rest which become NULL. Do you have two ID>2 within
the rows covered by your WHERE clause.
The HAVING apparently is able to prune out the NULL value.
Perhaps all you wanted was:
SELECT SUM(Orders.Sale_Type_Id) AS foreign
FROM Orders INNER JOIN
Processing ON Orders.ID = Processing.Order_ID
WHERE (Processing.Orderdate = '20050915') AND (Processing.status =1) AND
Orders.Sale_Type_ID > 2
RLF
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1127848228.326875.56120@.g49g2000cwa.googlegroups.com...
> I'm trying to set up my adhoc query to return just one single record,
> which is aliased as 'foreign' in my sql statement (which is just the
> total amount of foreign overseas orders for just one day. All
> Sale_Type_Ids over 2 [integer datatype] are foreign orders):
> SELECT SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) AS foreign
> FROM Orders INNER JOIN
> Processing ON Orders.ID = Processing.Order_ID
> WHERE (Processing.Orderdate = '20050915') AND (Processing.status =
> 1)
> GROUP BY CASE WHEN Orders.Sale_Type_Id > 2 THEN Orders.Sale_Type_Id
> ELSE NULL END
> HAVING (SUM(CASE WHEN Orders.Sale_Type_Id > 2 THEN
> Orders.Sale_Type_Id ELSE NULL END) >= 0)
> ..but my resultset is returning two records. If I remove the HAVING
> clause, it will return three records, with one being blank.
> ?
> .netsports
>|||If you only want a single row then remove the GROUP BY clause. GROUP BY
returns one row per group.
David Portas
SQL Server MVP
--|||Thanks. Looks like i'm getting the desired resultset. This sql
statement was sort of a permutation of an extensive one that would
bring back multiple records, but yes, I was starting to think if the
Group By was really necessary.|||I'm inferring your schema to be something like this:
CREATE TABLE Orders
(
ID INT PRIMARY KEY ?
, Sale_Type_Id INT
, Order_Total MONEY ?maybe
)
CREATE TABLE Processing
(
OrderID INT REFERENCES Orders(OrderID)
, Orderdate DATETIME
, Status INT
)
Your question sounds like you're either trying to find a count of
orders, or total amount purchased (which is why I made up that
ordertotal field). If either of those are what you're looking for,
there are much syntactically simpler solutions.
e.g.
SELECT COUNT(*) CountOfForeignOrders
, SUM(Order_Total) TotalOfForeignOrders
FROM Orders
WHERE Sale_Type_Id > 2 AND EXISTS(SELECT * FROM Processing WHERE
OrderID = Orders.ID AND OrderDate = '20050915' AND Status = 1)
When performing aggregates with join clauses, it's possible to
aggregate the same value more than once if a join causes the same row
to appear multiple times in the resultset. GROUP BY and HAVING aren't
necessary unless you're selecting your data based on your aggregate,
and if you'd return multiple aggregate sets.
-Alan
Group By - Count returns no rows
Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.
Since its just a count, you can check for @.@.ROWCOUNT after the SELECT. Or get the count into a variable which is set to 0 by default. so your variable will always have a value.
|||Interesting problem, I too thought at first glance that it would give you 1 row with value 0, but I tried it on one of my tables with the same results.
I think the problem is that WHERE clause, since there are no instances of Field2 that satisfy the GROUP BY there can be no rows returned at all.
I think the Dinakar is right, you'll have to check @.@.rowcount or something
|||IF EXISTS( {Your query} ) BEGIN {Your query} END ELSE BEGIN SELECT 0,NULL AS Field2 END
|||Thanks Motley, I came to this solution too but I thought that a better one could show up, sometimes you expect something more complicated and "elegant" shows up in the way, when the simple solution is in fact the way out of it. Thanks again!
GROUP BY
Hello!
I am using following dynamic stored procedure to return search results.
Evrything works fine... Now I would like to group results by IsFeatured
field, but I am receiving error message: "Column 'Table1.Id' is invalid in
the select list because it is not contained in either an aggregate function
or the GROUP BY clause."
Please help me!
James
----
CREATE PROCEDURE [_Search]
@.PriceMin Money = 0,
@.PriceMax Money = 0,
@.DistanceMin int = 0,
@.DistanceMax int = 0,
@.HighwayIds varchar(50) = NULL
AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql = 'SELECT
Table1.* FROM [Table1]'
IF @.HighwayIds IS NOT NULL
SELECT @.sql = @.sql + 'JOIN IntlistToTable(@.HighwayIds) S ON
Table1.HighwayId = S.number '
SELECT @.sql = @.sql + 'WHERE 1 = 1 '
IF (@.PriceMin <> 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between @.PriceMin And @.PriceMax)'
IF (@.PriceMin = 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between 0 And @.PriceMax)'
IF (@.DistanceFromMKADMin <> 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between @.DistanceMin And
@.DistanceMax)'
IF (@.DistanceFromMKADMin = 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between 0 And @.DistanceMax)'
SELECT @.sql = @.sql + ' And (IsActive = 1)'
SELECT @.paramlist = '@.PriceMin Money,
@.PriceMax Money,
@.DistanceMin int,
@.DistanceMax int,
@.HighwayIds varchar(50)'
EXEC sp_executesql @.sql, @.paramlist, @.PriceMin, @.PriceMax, @.DistanceMin,
@.DistanceMax, @.HighwayIdsPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Try using SET instead of SELECT in assignments, so it will look like
SQL/PSM.
Why do you write such procedural code? Think about this for two
seconds:
IF (@.price_min <> 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN @.price_min AND @.price_max)'
IF (@.price_min = 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN 0 AND @.price_max)'
Have you ever worked with a compiled language like SQL before? I see
that you confuse fields and columns, so you are VERY new to SQL.
Did you notice that you used the proprietary MONEY data type that has
funny arithmetic results? Then you use INTEGER in the predicates
(above) to force a type conversion. You give a procedure a useless
name with a leading underscore to destroy portability and create
maintenance problems. In the United States we have highway numbers
and not Highwayid; always use the standard terminology.
Of course you never try to pass a table as a list. You would load a
table with the highway numbers. Did you mean somethign like this?
CREATE PROCEDURE SearchForFoobars -- better name?
(@.price_MIN DECIMAL (12,4) = 0.00,
@.price_MAX DECIMAL (12,4) = 0.00,
@.distance_MIN INTEGER = 0,
@.distance_MAX INTEGER = 0)
AS
BEGIN - Validate parameters
IF @.price_min < 0.00 RAISERROR (..);
IF @.price_max < 0.00 RAISERROR (..);
IF @.distance_min < 0 RAISERROR (..);
IF @.distance_max < 0 RAISERROR (..);
SELECT Table1.* -- never use * in production code
FROM Table1, -- need a real name
WHERE Table1.highway_nbr
IN (SELECT highway_nbr FROM HighwayList)
AND price BETWEEN @.price_min AND @.price_max
AND distance BETWEEN @.distance_min AND @.distance_max
AND is_active = 1; -- flags! Just like assembly language
END;
Another alternative would be to put the highways into the parameter
list.
CREATE PROCEDURE SearchForFoobars
(..h1 INTEGER, h2 INTEGER,.., hn INTEGER)
..
WHERE Table1.highway_nbr
IN (SELECT COALESCE(h1, 0)
UNION ALL COALESCE(h2, 0)
.
SELECT COALESCE(hn, 0)) AS S(highway_nbr)
Now the proc can be compiled and maintained by a SQL programmer.|||Hi Joe,
A bit unrelated to the original post, but seeing your discussion of the
parameters to be passed to the stored proc, and a perceived deficiency
in my view recently:
Is there (or has there ever been proposed) a means of applying
constraints to parameters of procedures? By which I mean, a great many
stored procs get passed primary keys for particular tables, numeric
parameters which must fit within certain ranges, etc. I'd love to be
able to have:
Create Procedure Blah
@.BorisPK int,
@.LowerRange int,
@.HigherRange int
as
..
go
alter procedure Blah add constraint
Blah_BorisPK foreign key
(
@.BorisPK
)
references Boris
(
PK
)
go
alter procedure Blah add constraint
Blah_RangeWellDefined check
(
@.LowerRange > 0 and
@.HigherRange < 100 and
@.LowerRange <= @.HigherRange
)
go
The constraint system seems so good, I'd love to use it in more places
(and avoid having to write lots of checks within my SP). So, has this
ever been considered?
Damien
I am using following dynamic stored procedure to return search results.
Evrything works fine... Now I would like to group results by IsFeatured
field, but I am receiving error message: "Column 'Table1.Id' is invalid in
the select list because it is not contained in either an aggregate function
or the GROUP BY clause."
Please help me!
James
----
CREATE PROCEDURE [_Search]
@.PriceMin Money = 0,
@.PriceMax Money = 0,
@.DistanceMin int = 0,
@.DistanceMax int = 0,
@.HighwayIds varchar(50) = NULL
AS
DECLARE @.sql nvarchar(4000),
@.paramlist nvarchar(4000)
SELECT @.sql = 'SELECT
Table1.* FROM [Table1]'
IF @.HighwayIds IS NOT NULL
SELECT @.sql = @.sql + 'JOIN IntlistToTable(@.HighwayIds) S ON
Table1.HighwayId = S.number '
SELECT @.sql = @.sql + 'WHERE 1 = 1 '
IF (@.PriceMin <> 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between @.PriceMin And @.PriceMax)'
IF (@.PriceMin = 0) AND (@.PriceMax <> 0)
SELECT @.sql = @.sql + ' AND (Price Between 0 And @.PriceMax)'
IF (@.DistanceFromMKADMin <> 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between @.DistanceMin And
@.DistanceMax)'
IF (@.DistanceFromMKADMin = 0) AND (@.DistanceFromMKADMax <> 0)
SELECT @.sql = @.sql + ' AND (Distance Between 0 And @.DistanceMax)'
SELECT @.sql = @.sql + ' And (IsActive = 1)'
SELECT @.paramlist = '@.PriceMin Money,
@.PriceMax Money,
@.DistanceMin int,
@.DistanceMax int,
@.HighwayIds varchar(50)'
EXEC sp_executesql @.sql, @.paramlist, @.PriceMin, @.PriceMax, @.DistanceMin,
@.DistanceMax, @.HighwayIdsPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Try using SET instead of SELECT in assignments, so it will look like
SQL/PSM.
Why do you write such procedural code? Think about this for two
seconds:
IF (@.price_min <> 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN @.price_min AND @.price_max)'
IF (@.price_min = 0) AND (@.price_max <> 0)
SELECT @.sql = @.sql + ' AND (price BETWEEN 0 AND @.price_max)'
Have you ever worked with a compiled language like SQL before? I see
that you confuse fields and columns, so you are VERY new to SQL.
Did you notice that you used the proprietary MONEY data type that has
funny arithmetic results? Then you use INTEGER in the predicates
(above) to force a type conversion. You give a procedure a useless
name with a leading underscore to destroy portability and create
maintenance problems. In the United States we have highway numbers
and not Highwayid; always use the standard terminology.
Of course you never try to pass a table as a list. You would load a
table with the highway numbers. Did you mean somethign like this?
CREATE PROCEDURE SearchForFoobars -- better name?
(@.price_MIN DECIMAL (12,4) = 0.00,
@.price_MAX DECIMAL (12,4) = 0.00,
@.distance_MIN INTEGER = 0,
@.distance_MAX INTEGER = 0)
AS
BEGIN - Validate parameters
IF @.price_min < 0.00 RAISERROR (..);
IF @.price_max < 0.00 RAISERROR (..);
IF @.distance_min < 0 RAISERROR (..);
IF @.distance_max < 0 RAISERROR (..);
SELECT Table1.* -- never use * in production code
FROM Table1, -- need a real name
WHERE Table1.highway_nbr
IN (SELECT highway_nbr FROM HighwayList)
AND price BETWEEN @.price_min AND @.price_max
AND distance BETWEEN @.distance_min AND @.distance_max
AND is_active = 1; -- flags! Just like assembly language
END;
Another alternative would be to put the highways into the parameter
list.
CREATE PROCEDURE SearchForFoobars
(..h1 INTEGER, h2 INTEGER,.., hn INTEGER)
..
WHERE Table1.highway_nbr
IN (SELECT COALESCE(h1, 0)
UNION ALL COALESCE(h2, 0)
.
SELECT COALESCE(hn, 0)) AS S(highway_nbr)
Now the proc can be compiled and maintained by a SQL programmer.|||Hi Joe,
A bit unrelated to the original post, but seeing your discussion of the
parameters to be passed to the stored proc, and a perceived deficiency
in my view recently:
Is there (or has there ever been proposed) a means of applying
constraints to parameters of procedures? By which I mean, a great many
stored procs get passed primary keys for particular tables, numeric
parameters which must fit within certain ranges, etc. I'd love to be
able to have:
Create Procedure Blah
@.BorisPK int,
@.LowerRange int,
@.HigherRange int
as
..
go
alter procedure Blah add constraint
Blah_BorisPK foreign key
(
@.BorisPK
)
references Boris
(
PK
)
go
alter procedure Blah add constraint
Blah_RangeWellDefined check
(
@.LowerRange > 0 and
@.HigherRange < 100 and
@.LowerRange <= @.HigherRange
)
go
The constraint system seems so good, I'd love to use it in more places
(and avoid having to write lots of checks within my SP). So, has this
ever been considered?
Damien
Sunday, February 19, 2012
Gridview & SQL data source
I have a few Stored procedures that return values, some need parameters passing and others don't.
Up to now to access that data in a Web App I have called this procedure using VB after seting up a data block etc.
However I notice that the Web Developer express edition has some tools that look like they should help. I have used the in-built tools to create a SQL data source linked to a details view. I then specified that this should connect to my database (sql2005 hosted) and then specified Custom SQL or Stored procedure.
From the drop down I can then select the SP that purely returns values. When I try to test this query I get an error to say that the query did not return any data tables.
Is there a way to get values returned from a SP in this way?
Regards
Clive
You can use events like (Updating, Updated, Selecting, Selected etc) to do your cutomization. For example, if your method returns an output parameter then:
Sub OnDSUpdatedHandler(ByVal sourceAs Object,ByVal eAs SqlDataSourceStatusEventArgs)If e.AffectedRows > 0Then' Perform any additional processing, such as setting a status label. Label1.Text = Request.LogonUserIdentity.Name & _" changed user information sucessfully!" dim outputvalue asString outputvalue = e.Command.Parameters("yourparamname").Value.ToString()Else Label1.Text ="No data updated!"End If End Sub'OnDSUpdatedHandlerAs you can see, I haven't done any kind of error checking. Please ensure that your code does. If you need more help, reply to the post explaining the problem.
|||I think my problem is earlier than this stage to be honest.
For instance I have a SQL 2005 Stored Procedure that returns values from a table I can run this in SQL server itself and it works fine.
When definign this SP as a datasource using the detailsview object in Visual Web Developer I get the error I mentioend above when runnign the test option while I am stil in the wizard. So it looks liek some problem with callign the SP, yet it works OK natively.
|||
DetailsView, FormView, Gridview etc controls are designed to show a record/records. So, if you are using a SqlDataSource that just returns a single value, you cannot bind that to abovementioned controls. Can you explain why do you need to have a Detailsview that shows only single value?
Whenever asking a question, please add as much info as possible. Otherwise, we have to guess other parts (as you can see from my previous post) which is obviously not what you are looking for. Provide more info about your problem/scenario.
|||I have a stored procedure that takes no paramteres but returns some values which are calculated from a number of tables to returns some summary results. if I execute the SP from SQL manager I get 5 values returned which appear as output parameters.
I was hoping to be able to link these quickly and easily into a web page using a SQL data source linking to this SP as it's datasource.
I was hoping that the details view would then display the values returned.
There maybe an easier or better way to do this. I am a beginner with all this, and wanted to explore how to use a stored procedure as a datasource.
Regards
Clive
|||If the stored procedure returns resultset (table with columns and rows) then it would show nicely in the DetailsView, GridView etc. I don't think these controls have builtin ability to look for output parameters and bind those values. I think you should do this programmatically.
Connect to db, run the storedproc, get the results. Then you can either manually populate the values in controls or you can create a collection of values and bind it to a dataaware control. If you need sample code, then reply to the post mentioning your preference.
Grid pane in the table in enterprise manager
HI
When you right click open table to return all rows, click on the "Show/Hide Grid pane" icon . When you click on any row the first column called "Column" a dropdown shows all the filed names of that particular table.
This feature used to work fine untill recently wherein when I click on the column, I don't get to see all the filed names but now it just shows TableName.* i.e. all columns.
This is the case when I try to create a view in enterprise manager also. When you add a table to create a view, I cannot see all the filed names . I'm not sure what to do , to get the field names to appear? Is it some kind of setting in enterprise manager.
All the info would be appreciated.
Ta
NStop using Enterprise Manager (EM) and start using Query Analyzer (QA)
My own opinion
MOO
EM tends be quirky...|||I do use the Query analyser more than the EM, but it's just a functionality would reappear in the EM. Ta
When you right click open table to return all rows, click on the "Show/Hide Grid pane" icon . When you click on any row the first column called "Column" a dropdown shows all the filed names of that particular table.
This feature used to work fine untill recently wherein when I click on the column, I don't get to see all the filed names but now it just shows TableName.* i.e. all columns.
This is the case when I try to create a view in enterprise manager also. When you add a table to create a view, I cannot see all the filed names . I'm not sure what to do , to get the field names to appear? Is it some kind of setting in enterprise manager.
All the info would be appreciated.
Ta
NStop using Enterprise Manager (EM) and start using Query Analyzer (QA)
My own opinion
MOO
EM tends be quirky...|||I do use the Query analyser more than the EM, but it's just a functionality would reappear in the EM. Ta
Grid mode, Query Analyzer
Hi,
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
Thanks,
Oskar
I do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>
|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
mode
>
>.
>
|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...[vbcol=seagreen]
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
> developer, the grids in QA
> of the header and
> the data into the
> free!
> message
> mode
|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
loop[vbcol=seagreen]
this[vbcol=seagreen]
all[vbcol=seagreen]
Lite,[vbcol=seagreen]
For[vbcol=seagreen]
in a[vbcol=seagreen]
loop[vbcol=seagreen]
it's
>
>.
>
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
Thanks,
Oskar
I do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>
|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
mode
>
>.
>
|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...[vbcol=seagreen]
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
> developer, the grids in QA
> of the header and
> the data into the
> free!
> message
> mode
|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
loop[vbcol=seagreen]
this[vbcol=seagreen]
all[vbcol=seagreen]
Lite,[vbcol=seagreen]
For[vbcol=seagreen]
in a[vbcol=seagreen]
loop[vbcol=seagreen]
it's
>
>.
>
Grid mode, Query Analyzer
Hi,
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
Thanks,
OskarI do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
mode[vbcol=seagreen]
>
>.
>|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...[vbcol=seagreen]
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
>
> developer, the grids in QA
> of the header and
> the data into the
> free!
> message
> mode|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
loop[vbcol=seagreen]
this[vbcol=seagreen]
all[vbcol=seagreen]
Lite,[vbcol=seagreen]
For[vbcol=seagreen]
in a[vbcol=seagreen]
loop[vbcol=seagreen]
it's[vbcol=seagreen]
>
>.
>
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
Thanks,
OskarI do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
mode[vbcol=seagreen]
>
>.
>|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...[vbcol=seagreen]
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
>
> developer, the grids in QA
> of the header and
> the data into the
> free!
> message
> mode|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
loop[vbcol=seagreen]
this[vbcol=seagreen]
all[vbcol=seagreen]
Lite,[vbcol=seagreen]
For[vbcol=seagreen]
in a[vbcol=seagreen]
loop[vbcol=seagreen]
it's[vbcol=seagreen]
>
>.
>
Grid mode, Query Analyzer
Hi,
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
--
Thanks,
OskarI do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
--
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
>> Hi,
>> Does anyone know how to force Query Analyzer in Grid
mode
>> to return results in chunks like it is in Text Mode? For
>> instance, when I have a query that returns results in a
>> loop, in Grid mode I would have to wait until the loop
>> exits to see the whole lot of them. With Text mode it's
>> different - I see results as I go.
>> --
>> Thanks,
>> Oskar
>
>.
>|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
> >--Original Message--
> >I do not think this is possible. Speaking as a
> developer, the grids in QA
> >auto adjusts their width taking into account the length
> of the header and
> >data, which would only be possible once you've loaded all
> the data into the
> >grid.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
> free!
> >
> >
> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> Does anyone know how to force Query Analyzer in Grid
> mode
> >> to return results in chunks like it is in Text Mode? For
> >> instance, when I have a query that returns results in a
> >> loop, in Grid mode I would have to wait until the loop
> >> exits to see the whole lot of them. With Text mode it's
> >> different - I see results as I go.
> >>
> >> --
> >> Thanks,
> >> Oskar
> >>
> >
> >
> >.
> >|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
>> I can't see a problem here: each result returned in a
loop
>> has its own headers and data, the length of which should
>> be known. Maybe Query Analyzer just can't/won't read
this
>> information during the execution?
>> --
>> Thanks,
>> Oskar
>> >--Original Message--
>> >I do not think this is possible. Speaking as a
>> developer, the grids in QA
>> >auto adjusts their width taking into account the length
>> of the header and
>> >data, which would only be possible once you've loaded
all
>> the data into the
>> >grid.
>> >
>> >--
>> >Peter Yeoh
>> >http://www.yohz.com
>> >Need smaller SQL2K backup files? Use MiniSQLBackup
Lite,
>> free!
>> >
>> >
>> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> Does anyone know how to force Query Analyzer in Grid
>> mode
>> >> to return results in chunks like it is in Text Mode?
For
>> >> instance, when I have a query that returns results
in a
>> >> loop, in Grid mode I would have to wait until the
loop
>> >> exits to see the whole lot of them. With Text mode
it's
>> >> different - I see results as I go.
>> >>
>> >> --
>> >> Thanks,
>> >> Oskar
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Does anyone know how to force Query Analyzer in Grid mode
to return results in chunks like it is in Text Mode? For
instance, when I have a query that returns results in a
loop, in Grid mode I would have to wait until the loop
exits to see the whole lot of them. With Text mode it's
different - I see results as I go.
--
Thanks,
OskarI do not think this is possible. Speaking as a developer, the grids in QA
auto adjusts their width taking into account the length of the header and
data, which would only be possible once you've loaded all the data into the
grid.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> Hi,
> Does anyone know how to force Query Analyzer in Grid mode
> to return results in chunks like it is in Text Mode? For
> instance, when I have a query that returns results in a
> loop, in Grid mode I would have to wait until the loop
> exits to see the whole lot of them. With Text mode it's
> different - I see results as I go.
> --
> Thanks,
> Oskar
>|||I can't see a problem here: each result returned in a loop
has its own headers and data, the length of which should
be known. Maybe Query Analyzer just can't/won't read this
information during the execution?
--
Thanks,
Oskar
>--Original Message--
>I do not think this is possible. Speaking as a
developer, the grids in QA
>auto adjusts their width taking into account the length
of the header and
>data, which would only be possible once you've loaded all
the data into the
>grid.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
>> Hi,
>> Does anyone know how to force Query Analyzer in Grid
mode
>> to return results in chunks like it is in Text Mode? For
>> instance, when I have a query that returns results in a
>> loop, in Grid mode I would have to wait until the loop
>> exits to see the whole lot of them. With Text mode it's
>> different - I see results as I go.
>> --
>> Thanks,
>> Oskar
>
>.
>|||You mean there are multiple result sets returned? Then you're right, QA
(theoretically) should be able to display each result set as they come in.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Oskar" <anonymous@.discussions.microsoft.com> wrote in message
news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
> I can't see a problem here: each result returned in a loop
> has its own headers and data, the length of which should
> be known. Maybe Query Analyzer just can't/won't read this
> information during the execution?
> --
> Thanks,
> Oskar
> >--Original Message--
> >I do not think this is possible. Speaking as a
> developer, the grids in QA
> >auto adjusts their width taking into account the length
> of the header and
> >data, which would only be possible once you've loaded all
> the data into the
> >grid.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
> free!
> >
> >
> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> Does anyone know how to force Query Analyzer in Grid
> mode
> >> to return results in chunks like it is in Text Mode? For
> >> instance, when I have a query that returns results in a
> >> loop, in Grid mode I would have to wait until the loop
> >> exits to see the whole lot of them. With Text mode it's
> >> different - I see results as I go.
> >>
> >> --
> >> Thanks,
> >> Oskar
> >>
> >
> >
> >.
> >|||You're right, multiple result sets.
>--Original Message--
>You mean there are multiple result sets returned? Then
you're right, QA
>(theoretically) should be able to display each result set
as they come in.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"Oskar" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2de601c4b1c0$5c2592e0$a501280a@.phx.gbl...
>> I can't see a problem here: each result returned in a
loop
>> has its own headers and data, the length of which should
>> be known. Maybe Query Analyzer just can't/won't read
this
>> information during the execution?
>> --
>> Thanks,
>> Oskar
>> >--Original Message--
>> >I do not think this is possible. Speaking as a
>> developer, the grids in QA
>> >auto adjusts their width taking into account the length
>> of the header and
>> >data, which would only be possible once you've loaded
all
>> the data into the
>> >grid.
>> >
>> >--
>> >Peter Yeoh
>> >http://www.yohz.com
>> >Need smaller SQL2K backup files? Use MiniSQLBackup
Lite,
>> free!
>> >
>> >
>> >"Oskar" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:086101c4b1b2$df5fd070$a401280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> Does anyone know how to force Query Analyzer in Grid
>> mode
>> >> to return results in chunks like it is in Text Mode?
For
>> >> instance, when I have a query that returns results
in a
>> >> loop, in Grid mode I would have to wait until the
loop
>> >> exits to see the whole lot of them. With Text mode
it's
>> >> different - I see results as I go.
>> >>
>> >> --
>> >> Thanks,
>> >> Oskar
>> >>
>> >
>> >
>> >.
>> >
>
>.
>
Subscribe to:
Posts (Atom)