*facepalms* That did it. I wasn't sorting the query by Client_Name. I was doing
it by Client_Code.
Thanks guys. I appricate the help. :)
> > group="Client_Code">
>
> It should work as long as its contained without a nested cfoutput as
> Dave mentioned. Also, notice he is grouping by "Client_N
> group="Client_Code">
It should work as long as its contained without a nested cfoutput as Dave
mentioned. Also, notice he is grouping by "Client_Name" instead of the code.
Be sure the query results are sorted by that column too.
-Leigh
~
Hrm.. Still getting the same result.
The code that is actually printing it is
#currentQuery[currentField][currentQuery.currentRow]#
So I probably have to figure out how to remove the current row (since
#currentQuery[currentField]# doesn't work). It's probably forcing it to display
just on th
> So I have a query that returns the following..
>
> Client_Code MenuName Client_Name
> 4938 Test Test Company
> 9328 Test Test Company
> 10349 Test Test Company
> 9283 Test Test 2 Company
>
> Wh
So I have a query that returns the following..
Client_Code MenuName Client_Name
4938 Test Test Company
9328 Test Test Company
10349 Test Test Company
9283 Test Test 2 Company
What I'm trying to
What issue are you having with your code?
~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/messa
I have a result set that has the following columns
studentid,sectionid,tracedate,duration,pagehits
I want to draw a horizontal bar chart per student per section
displaying tracedate as xaxis and duration and pagehits as the yaxis one below
the other
Thanks
~
That was it.
I found the solution
SELECT
DATENAME(mm, article.Created) AS Month,
DATENAME(, article.Created) AS Year,
COUNT(*) AS Total
FROM Articles AS article
GROUP BY
DATENAME(mm, article.Created),
DATENAME(, article.Created)
ORDER BY Month, Year DESC
At the
Your group by has to match your select in most cases...
Also, you are using reserved words in your query so that can mess stuff
up.
Try this instead...
Select count(intMemberID) as [NewMembers], dateName(mm, [dteAdded]) +
'-' + dateName(, [dteAdded]) as MTH
>From table
Group b
If this is SQL Server, than the GROUP BY has to match the un-aggregated
SELECT columns, so it would need to be something like:
SELECT COUNT(intMemberID) AS NewMembers,
DATENAME(mm, dteAdded) + '-' + DATENAME(, dteAdded) AS Month
FROM TABLE
GROUP BY DATENAME(mm
How can I get this query to group by month?
select count(intMemberID)as NewMembers, DATENAME(mm, dteAdded) + '-' +
DATENAME(, dteAdded) AS Month
FROM TABLE
GROUP BY Month(dteAdded)
When I ask on google everyone says to add:
*GROUP BY Year(SomeDate), Month(SomeDate)
*
but that do
Diego (W), New Orleans (S)
Is that the kind of result set you are looking for?
Dave
-Original Message-
From: Les Mizzell [mailto:lesm...@bellsouth.net]
Sent: Friday, October 02, 2009 11:10 AM
To: cf-talk
Subject: GROUP BY problem...
SELECT
employees.empID
employees.name
cated information.
You could make multiple columns "address1", "address2", etc but that is
considered really bad database design and isn't normalized.
Steve
-Original Message-
From: Les Mizzell [mailto:lesm...@bellsouth.net]
Sent: Friday, October 02, 2009 12:58 PM
To:
Agha Mehdi wrote:
> Why not just
>
> #name#
>
> #area_name#, #office_name#
>
>
>
I could - but it's way more complicated than that...
This single query (example was simplified) is responsible for a number
of pages...
Case A: multiple results based o
, here's the problem
>
> I need to be able to "GROUP BY" employees.empID so each employee is
> returned only ONCE (a unique empID), but, I can't include any of the
> columns in the other tables in the "GROUP BY", because there may be
> multiple records fr
02, 2009 12:10 PM
To: cf-talk
Subject: GROUP BY problem...
SELECT
employees.empID
employees.name,
employees_office.theORDER
office.office_name
areas.area_NAME
FROM employees
INNER JOIN employees_office ON employees.empID = employees_office.empID
INNER JOIN office ON
areas.empID = employees_office.empID
ORDER BY employees.name, employees_office.theORDER
OK, here's the problem
I need to be able to "GROUP BY" employees.empID so each employee is
returned only ONCE (a unique empID), but, I can't include any of the
columns in the other tables in the
I don't see how that SQL would work. You are using deptName in your select
list and order by, but you aren't grouping by it. Unless MySQL allows you
to do stuff that SQL Server doesn't, that should throw an error.
Also, I had originally suggested placing the amount in the
Sorry, forgot to include the revised script ...
= ===
OK, think I've got it now ...
GROUP BY contains the main criteria for the query (not to be confused with
ORDER BY)
HAVING should contain any (both) conditions needed for the search.
OK, think I've got it now ...
GROUP BY contains the main criteria for the query (not to be confused with
ORDER BY)
HAVING should contain any (both) conditions needed for the search.
http://ttcfm.open.ac.uk/~bs3578/test1/Week9_Act6.cfm
thanks
BTWare there any tutorials online
Try putting a.Amount in your group by clause. Everything in your select
list should be in the group by OR should have an aggregate function
applied to it.
Also, I noticed you are referencing an alias "AverageAward" in your
order by. I'm pretty certain SQL Server doesn't
Do this:
SELECT d.DeptName AS dName,
AVG(a.Amount) AS AverageAward,
SUM(a.Amount) AS DeptTotal
You can't select individual amounts if you're performing aggregates on that
same column.
Also, your SUM(a.Amount) > 5000 belongs probably needs to go in the HAVING
clause.
~~~
,
SUM(a.Amount) AS DeptTotal
FROM incentiveawards a
INNER JOIN employeedirectory e ON (e.ID = a.RecipientIDfk)
INNER JOIN departments d ON (d.DepartmentID = e.DepartmentIDfk)
WHERE SUM(a.Amount) > 5000
GROUP BY d.DeptName
HAVING AVG(a.Amount) > 3000
OR
Sorry : my mistake, I was mistaking this thread for "Is there a
non-aggregate Max() function in MySQL?"
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doublecli
>>you can also do:
#arraysum(queryname.columnname)#
Any CF function will be executed before the query by CF.
What is needed here is an SQL function executed inside the query.
~|
Adobe® ColdFusion® 8 software 8 is the most import
you can also do:
#arraysum(queryname.columnname)#
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Tech Gate wrote:
>
> select a.Dept, c.Assonum
> .
> group by a.Dept, c.Assonum
>
>
> I need to get the SUM of c.Assonum, but I can't do sum
Yup. I got it.
Thanks so much
On Fri, Dec 12, 2008 at 8:19 PM, Jason Fisher wrote:
> There's not a function, but you can add them up as you loop over the query:
>
>
>
>
>
> SUM of Assonum is #sumAssonum#
>
>>
>> select a.Dept, c.Assonum
>&
There's not a function, but you can add them up as you loop over the query:
SUM of Assonum is #sumAssonum#
>
> select a.Dept, c.Assonum
> .........
> group by a.Dept, c.Assonum
>
>
> I need to get the SUM of c.Assonum, but I can't do sum(c.Assonum ) in
>
select a.Dept, c.Assonum
.
group by a.Dept, c.Assonum
I need to get the SUM of c.Assonum, but I can't do sum(c.Assonum ) in
the sql (cfquery).
What is the way to get the SUM of c.Assonum with cf function?
Please advise.
Than
;
>
> select * from query1
> group by column1, column2;
>
>
> but it gives me the following error:
>
> The column query.column3 is invalid in the SELECT list clause because it is
> not contained in either an aggregate function or the GROUP BY clause.
>
> it doesnt
hi
i am trying to run this query of query:
select * from query1
group by column1, column2;
but it gives me the following error:
The column query.column3 is invalid in the SELECT list clause because it is not
contained in either an aggregate function or the GROUP BY clause.
it doesnt
;re right
> Charlie about that assuming the file extension is only 3 characters, however
> two file extensions with the same last 3 characters isn't all that likely.
>
> Now, I'm having a little trouble with this group by:
>
>
> #VARIABLES.Messages.type#
>
An ORDER BY?
Adrian
-Original Message-
From: Robert Rawlins - Think Blue
[mailto:[EMAIL PROTECTED]
Sent: 23 July 2007 16:45
To: CF-Talk
Subject: RE: GROUP BY RIGHT()?
Thanks guys for the suggestion,
I'll agree that the calculated column is probably my best bet, you're rig
ing a little trouble with this group by:
#VARIABLES.Messages.type#
For some reason it doesn't appear to group properly, either that or I'm
forgetting how to use it properly :-D That code snippet outputs like this:
Gif
Jpg
Gif
Txt
Gif
Vcf
See how it's not grouping the
):
select filename, right(filename, 3) as lastThreeFileName
from my table
order by lastThreeFileName
On 7/23/07, Robert Rawlins - Think Blue <[EMAIL PROTECTED]>
wrote:
>
> Hello Guys,
>
>
>
> I've got a query which returns a load of file names which I'm looking to
&g
run an instr() function inside of a CASE to determine the
position of the dot if need be.
On 7/23/07, Robert Rawlins - Think Blue
<[EMAIL PROTECTED]> wrote:
> Hello Guys,
>
>
>
> I've got a query which returns a load of file names which I'm looking to
> group by th
Developer
www.bennadel.com
Need ColdFusion Help?
www.bennadel.com/ask-ben/
-Original Message-
From: Robert Rawlins - Think Blue
[mailto:[EMAIL PROTECTED]
Sent: Monday, July 23, 2007 11:11 AM
To: CF-Talk
Subject: GROUP BY RIGHT()?
Hello Guys,
I've got a query which returns a load of f
Hello Guys,
I've got a query which returns a load of file names which I'm looking to
group by their extension. Is there any way to do this dynamically from the
name or do I have to make a separate column in the database?
Can I do something like this:
#myquer
Perfect Deanna,
That works nicely, glad I've got you guys on hand, I would have NEVER gotten
that one on my own.
Thanks a million,
Rob
-Original Message-
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: 01 May 2007 14:23
To: CF-Talk
Subject: Re: Group By Hours
It's p
It's pretty much as you said, except the syntax is:
SUM(CASE WHEN logclass_id = 1 THEN 1 ELSE 0 END) as sent
On 5/1/07, Robert Rawlins - Think Blue wrote:
> Ok,
>
>
> COUNT(WHERE LogClass_ID = 1) AS Sent
> COUNT(WHERE LogClass_ID = 2) AS Failed
> COUNT(WHERE LogClass_ID = 3) AS Postponed
>
~~~
,
DATEPART(year, DateTime) AS myYear,
DATEPART(hour, DateTime) AS myHour,
COUNT(1) AS SentOk
FROMMacLog
WHERE DateTime BETWEEN AND
AND ThinkTank_ID =
GROUP BYDATEPART(month, dateTime
Don't Worry,
Onto something now with that date part stuff.
I'll let you know how I get on.
Rob
-Original Message-
From: Robert Rawlins - Think Blue
[mailto:[EMAIL PROTECTED]
Sent: 01 May 2007 13:48
To: CF-Talk
Subject: RE: Group By Hours
Thanks for the suggestion Pete,
I&#
01 May 2007 13:26
To: CF-Talk
Subject: Re: Group By Hours
select logid, classid, datetime, datepart(year, datetime) + '/' +
datepart(month, datetime) + '/' + datepart(day, datetime) + '/' +
datepart(hour, datetime) AS groupbyvalue
from table
order by datetime
That loo
select logid, classid, datetime, datepart(year, datetime) + '/' +
datepart(month, datetime) + '/' + datepart(day, datetime) + '/' +
datepart(hour, datetime) AS groupbyvalue
from table
order by datetime
That looks sort of weird, but I think what you'll need to do is make sure
you're grouping by mor
Check out datepart.
-Original Message-
From: Robert Rawlins - Think Blue
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 01, 2007 7:42 AM
To: CF-Talk
Subject: Group By Hours
Hello Guys,
I have a bunch of records of log data, all with a datetime stamp on it,
and I'm looking to hav
Hello Guys,
I have a bunch of records of log data, all with a datetime stamp on it, and
I'm looking to have SQL Server return them grouped into hours. I've done
this before using MySQL I think, using the Hour() function in the query but
it would seem that SQL Server doesn't support such a funct
arrgh, couldn't work it out so have created a list and appended to it on each
output so i can use a listfind to prevent duplicate ID's happening. rough but
works.
thanks for your help.
~|
Upgrade to Adobe ColdFusion MX7
Experi
select id, max(otherfield) as otherfield
from table, othertable
where table.id = othertable.id
group by id
order by otherfield
This is just off the top of my head, but I hope it gets the idea across.
--Ben Doom
Mike Little wrote:
> not sure i follow ben, i don't want to include the
not sure i follow ben, i don't want to include the category/collection info in
the GROUP BY as this will give me the exact same result as not using GROUP BY?
could you give an example of using the min/max on the other columns?
thanks mate.
mike
>When you instruct the system to onl
When you instruct the system to only return one of a group like that,
you have to tell it which of the possible selections to make. So, you
need to either group by the other columns (which will return a row for
every distinct entry in that column) or use an aggregate function to
only return
Hi guys,
Got a product query (see below) - just want to make sure that not more
than one of each product_id is displayed on a page. Tried the GROUP BY
function
Eg. GROUP BY products.product_id
But I get the error...
[Macromedia][SQLServer JDBC Driver][SQLServer]Column
'products.product_
Message-
From: Mike Little [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 30, 2006 6:39 PM
To: CF-Talk
Subject: Re: GROUP BY error
hmmm...
now i cannot get at my getArticles.image_count variable ??
> sort of like this?
>
> SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_
hmmm...
now i cannot get at my getArticles.image_count variable ??
> sort of like this?
>
> SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified,
> clubroom.cb_content, clubroom.cb_isArchive,
> (
> SELECT COUNT(cbi_id) AS image_count
> FROM clubroom_images
> )
> FRO
sort of like this?
SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified,
clubroom.cb_content, clubroom.cb_isArchive,
(
SELECT COUNT(cbi_id) AS image_count
FROM clubroom_images
)
FROM clubroom LEFT OUTER JOIN clubroom_images
ON clubroom.cb_id = clubroom_images.cb_id
You could either do something like selecting the content as a substring
(which will work in group by) or you could skip the Group BY and run the
count as sub-query
SELECT
...
SUBSTRING( clubroom.cb_content, 0, 1000 ) AS cb_content
...
FROM
...
GROUP BY
Hi guys,
I have the following query...
SELECT clubroom.cb_id, clubroom.cb_dCreated, clubroom.cb_dModified,
clubroom.cb_content, clubroom.cb_isArchive,
COUNT(clubroom_images.cbi_id) AS image_count
FROM clubroom LEFT OUTER JOIN clubroom_images
ON clubroom.cb_id = clubroom_images.cb_id
GROUP BY
Not really, it's just a habit. I like to explicitlly control the name of the
resulting variable.
Dave is right, as usual, you can just leave it and it will retain it's given
column name.
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
> Question: What's the benefit to using the alias? Is there a
> benefit or is its usage just required?
>
> Could it just be:
>
> Select distinct Book_Genre
> from Books
> order by Book_Genre
You don't need an alias. "SELECT DISTINCT fieldname FROM tablename" will do.
Dave Watts, CTO, Fig L
Yep...it was one of those "duh" moments...
Thanks for your help.
Rick
-Original Message-
From: Dave Watts [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 4:41 PM
To: CF-Talk
Subject: RE: Group By function for MS SQL Server Express 2005?
> Just a list of the genre
Book_Genre
???
Rick
-Original Message-
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 3:55 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
SELECT DISTINCT(Book_Genre) AS MyGenre
FROM Books
ORDER BY Book_Genre
This will accomplis
> Just a list of the genres to fill out a dropdown list...
SELECT DISTINCT Genre FROM Book
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore
SELECT DISTINCT(Book_Genre) AS MyGenre
FROM Books
ORDER BY Book_Genre
This will accomplish what you want.
=]
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~|
Message: http://www.houseoffusion.com/list
Just a list of the genres to fill out a dropdown list...
-Original Message-
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 1:16 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
Are you trying to get a count of all of the books by
> Ok...I had read a message that mentioned the aggregate
> function requirement...but how would I write my example
> query to include an (unneeded?) aggregate function?
>
> (And why would there be an aggregate requirement anyway?)
>
> Query (MySQL version):
>
> S
IL PROTECTED]
> Sent: Tuesday, June 13, 2006 1:05 PM
> To: CF-Talk
> Subject: Re: Group By function for MS SQL Server Express 2005?
>
> But it is needed, that query is returning the count of the books per
> book_genre
>
> On 6/13/06, Rick Faircloth <[EMAIL PROTECTED]&
But why does it require that the count be calculated
if I don't need that information?
-Original Message-
From: Greg Morphis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 13, 2006 1:05 PM
To: CF-Talk
Subject: Re: Group By function for MS SQL Server Express 2005?
But it is n
Are you trying to get a count of all of the books by Genre? Or are you
trying to get a list of all of the genres?
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~|
Message: http://www.houseoffusion.com/li
Ok, I see what's going on. You want to out put your records in a group,
thats a CF issue, not a SQL issue.
What you need to do is to use ORDER BY to get your records in the correct
order then user the GROUP attribute of the CFOUTPUT tag
GROUP BY in SQL is for compiling related dat
s Expr2, Book_Genre
> from Books
> Group By Book_Genre
> Order By Book_Genre
>
> Seems strange to require an unneeded aggregate function...
>
> Rick
>
>
>
>
> -Original Message-
> From: Rick Faircloth [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 13,
I finally did get Express to give me the results I wanted...
Here's the solution the Query Builder gave:
Select Count(*) as Expr2, Book_Genre
from Books
Group By Book_Genre
Order By Book_Genre
Seems strange to require an unneeded aggregate function...
Rick
-Original Message-
Ok...I had read a message that mentioned the aggregate function
requirement...but
how would I write my example query to include an (unneeded?) aggregate
function?
(And why would there be an aggregate requirement anyway?)
Query (MySQL version):
Select * from books group by genre
Here is a simple example using the Northwind database in SQL Server
The key to the GROUP BY clause is that is needs to be used in aggregate
functions
SELECT COUNT(P.ProductID) AS ItemTotal, C.CategoryName
FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID
GROUP BY
I've used it.
Can you show us the SQL you are having a problem with?
--
Alan Rother
Macromedia Certified Advanced ColdFusion MX 7 Developer
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243360
Archives: http://ww
Hi, all.
Does MS SQL Server Express 2005 or even MS SQL Server
not use a "Group By" function?
Rick
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:243357
Archives: http://www.houseoffusion.com/cf_lists/t
Thanks, I found a way to do it. Just had to add a field to the
Table to hold the WeekNumber and the GROUP by WEEKNUM ;-)
Thanks!
-Original Message-
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 17, 2006 10:18 PM
To: CF-Talk
Subject: RE: MS-SQL Group by Week
Check out this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6228
M!ke
-Original Message-
From: Coldfusion [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 17, 2006 9:07 PM
To: CF-Talk
Subject: MS-SQL Group by Week
Does anyone know if it is possible via Query to group
Does anyone know if it is possible via Query to group results by Week when
the field actually
contains a full date?
I have a query where I have to pull all data and the SUM of a field which
all should be grouped
by Week such as:
Week 1 (01/01/2006 - 01/07/2006)
John Smith
Project 5: 15hrs
I wonder... what database are you using? SQL server has always been pretty
helpful with that error
-Original Message-
From: daniel kessler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 12, 2005 2:28 PM
To: CF-Talk
Subject: Re: query sum or group by
oh thank you thank you thank you
on (sum, count, max, min, etc)
>columns and regular columns without using group by. Every column in
>the select statement must either have an aggregate function, or must
>be in the group by clause. What you're trying to do above will work if
>you add food_item and pkg_size to the grou
Here's the rule. It's pretty simple.
You can not select aggregate function (sum, count, max, min, etc)
columns and regular columns without using group by. Every column in
the select statement must either have an aggregate function, or must
be in the group by clause. What you're try
umn) as totalpricebyitem, COUNT(itemid)
>AS countbyitem
>FROM yourtables
>group by itemname
I'm sorry. I didn't mean to confuse. It's a very similar query as the first
one, but it is a different query.
You're right, except I'm not doing a SUM on the price pe
em, COUNT(itemid)
AS countbyitem
FROM yourtables
group by itemname
On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote:
> >When you ask Oracle to group by a particular column, it looks for like
> >values within that column and groups those rows of data together.
> >L
>When you ask Oracle to group by a particular column, it looks for like
>values within that column and groups those rows of data together.
>Let's look at some sample data.
>
>CustID PricePaid Date
>1 .505/1/05
>1 .
I gave it a try, with the where clause duplicating the outside one, but it gave
me an unusually large number, so I'll have to give it a bit more time.
thanks!
>Of course, that total_price subselect will undoubtedly need to be
>altered with a where clause too, but you know your data, so play wi
Of course, that total_price subselect will undoubtedly need to be
altered with a where clause too, but you know your data, so play with
that some until it's the specific total you want. Then you will have
that column containing the total alongside all your others.
Ken Ferguson wrote:
> How doe
ore_id = s.food_store_id
>> AND g.gi_id = #cookie.fsnep_bargain_hunt_gi_id#
>>GROUP BY p.si_id,s.food_store_id,s.gs_price
>>
>>
>
>Remove the s.gs_price from the select clause and the group by clause
>if you want a sum on this column:
>
>SELEC
On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote:
> I guess I could also replicate the query with another name and just do a
> sum there.
Ack. Don't do that. If you want to learn how to do it in a sub-query,
then by all means, research subqueries. But doing an extra trip back
to the DB is
>You could also do it with a sub-query. But, this'll be faster and
>cleaner if you just want the total of all columns for display.
I see. I had incorrectly assumed that they could all occupy the SELECT line
and that both types of functionality could be done in one query. I guess I
could also r
#
(single row data here)
Total: #arraySum(myquery["gs_price"])#
You could also do it with a sub-query. But, this'll be faster and
cleaner if you just want the total of all columns for display.
On 7/12/05, daniel kessler <[EMAIL PROTECTED]> wrote:
> ok, it sounds like I s
ok, it sounds like I shouldn't have a GROUP BY at all. I already did the
search that I want and now I just want to total everything.
If I remove everything from the SELECT and remove the GROUP BY then it seems to
total and doesn't give an error, so mini-yay!
It seems though that I
When you ask Oracle to group by a particular column, it looks for like
values within that column and groups those rows of data together.
Let's look at some sample data.
CustID PricePaid Date
1 .505/1/05
1 .75 5/7/05
2
Alright, I did that but I have a few questions.
First, I still received a total_price of 0.60 with 15 records return of which
the first several records had a price of 0.60.
Secondly, why should I have removed them from the SELECT and GROUP BY? I'd
like to understand better.
Also, in this
> SELECT p.si_id,s.food_store_id,s.gs_price, SUM(s.gs_price) AS total_price
> FROM fsnep_food_store_purchases p, fsnep_food_store
> s,fsnep_food_store_game_info g
> WHERE p.food_store_id = s.food_store_id
>AND g.gi_id = #cookie.fsnep_bargain_hunt_gi_id#
> GROUP BY p.si_
I am trying to SUM a column in oracle. The query works fine until I add the
SUM. It seems from the error, that I then have to have a GROUP BY. I guess I
don't understand how to use the GROUP BY in this instance or choose which
column to GROUP BY. I have googled around.
SELECT p.
Jochem,
Thx.
I've reread your posts a few times. It will help next time.
-- gil
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 23, 2005 6:41 PM
To: CF-Talk
Subject: Re: group by date, missing something
mayo wrote:
>
> SELECT MON
mayo wrote:
>
> SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal
> FROM sales
> GROUP by theMonth
I know you already know how to solve your problem, but please
bear with me :)
The reason that your query isn't supposed to work (it will work
in some impleme
Thx barneyb for the hint:
The solution is:
SELECT MONTH(saleDate) AS theMonth, YEAR(saleDate) AS theYear,
sum(total) as dailyTotal
FROM sale
GROUP by MONTH(saleDate), YEAR (saleDate)
ORDER BY YEAR(saleDate), MONTH(saleDate)
-- thx, gil
-Original Message
Thanks eric, but that gives a total for every day.
June 21 10,000
June 20 9,555
June 19 11,200
That's useful but I'm trying to get the total sales for each month and
need to do a group by for the month
June ... 250,000
May ... 430,000
Apr ... 380,000
It
This will work, but it will group by dates down to the second. So that means
the query won't return any grouped records unless they actually occurred on the
exact same second.
You need to use DATEPART() or a similar function, to group by the Year and the
Month such as MM. That i
SELECT sum(saleTotal), saledate
FROM sales
GROUP BY saledate
- Original Message -
From: "mayo" <[EMAIL PROTECTED]>
To: "CF-Talk"
Sent: Wednesday, June 22, 2005 6:29 PM
Subject: group by date, missing something
> I'm trying to do a group by date on
1 - 100 of 217 matches
Mail list logo
