Cool, yeah I never remember until I do it when an aggregate query is going to
want HAVING vs WHERE. Glad it's working for you!
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free
- Original Message -
From: Jason Fisher [EMAIL PROTECTED]
Cool, yeah I never remember until I do it when an aggregate query is going
to want HAVING vs WHERE. Glad it's working for you!
This bites me too when I'm not paying attention. Just remember that the
WHERE applies to the
In MS SQL Server it's ISNULL(), but can't speak for other platforms. Can't
recall what it is in Oracle, might just be NULL().
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free
In MS SQL Server it's ISNULL(), but can't speak for other platforms.
Can't recall what it is in Oracle, might just be NULL().
IIRC in Oracle it is NVL. There is also COALESCE, which is usually a safe bet
with most databases.
didn't work, got an error. changed the isnull to ifnull, got a invalid use of
a group function error... I don't even know how to fix that...=(
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points, p.
short_description, p.quantity,
NM, got it... changed the where clause to having and moved it below the group
by... seems to be working so far!
Thanks!
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points,
p.short_description, p.quantity, p.image
FROM tblproducts as p LEFT JOIN
tblorder_list as o ON p.sku = o.sku #can_afford#
WHERE SUM(ISNULL(o.qty, 0)) p.quantity
GROUP BY p.sku
i believe the correct function to use is IFNULL(), not ISNULL()...
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/
Jason Fisher wrote:
Try this, I think it's what you're looking for:
SELECT SUM(ISNULL(o.qty, 0)) as sold, p.sku, p.name, p.points,
p.short_description, p.quantity,
thanks to everyone who replied, problem solved.
Jenny
-Original Message-
From: Rafael Marquez [mailto:[EMAIL PROTECTED]
Sent: 04 April 2007 21:06
To: CF-Talk
Subject: RE: SQL Query Help
Well, it's not a brain fart. That query is kinda kinky.
Use this as an example, replacing your
Maybe something like this (which I cannot test):
SELECT
c.id,
c.name,
i.*
FROM
customer c
LEFT OUTER JOIN
(
-- Get max (most recent) id (assuming pkey) of
-- each invoice as grouped by client.
Assuming the invoice numbers increase each time, you can use MAX() and
GROUP BY to get what you need.
SELECT customer, MAX(invoiceNum) AS lastNum
FROM table
GROUP BY customer
If invoices are not numbered sequentially, hopefully you have a date
field that you can add to the grouping.
M!ke
Need more info: What would the query be to return the last invoice for a
single customer?
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 8:58 AM
To: CF-Talk
Subject: SQL Query Help
having brain fart here ... ughh
scenario:
(T.Deleted = 0) AND (T.Status 'Pending') AND (T.AccountType =
'Customer')
GROUP BY T.CustomerID, T.TransactionDate, T.ID
Hope this helps.
-Original Message-
From: Gaulin, Mark [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 04, 2007 3:39 PM
To: CF-Talk
Subject: RE: SQL Query Help
Mik Muller wrote:
select left(datetime,11), count(*) as dt
from sitelog
where datetime = '2007-02-03 00:00'
group by left(datetime,11)
order by left(datetime,11) desc
Well, now that I look at it, the order by is ordering by jan 1 2006 etc in
This is how I'm doing it for now...
select count(*) as dt cfif url.stats eq bar-day, left(datetime,11) as
datetime/cfif
from sitelog
where datepart(year,datetime) = '#theyear#'
and datepart(month,datetime) = '#themonth#'
cfif listLen(url.datestat,/) eq 2
and datepart(year,datetime) =
Elena Aminova wrote:
id DateQty Dollars
1605/30/2003 4 890.00
2707/28/2004 1 300.00
2901/17/2003 4 108.00
5501/21/2002 1 105.00
5609/20/2003 7 700.00
5609/16/2003 0 0.00
56
2.) Can someone please suggest a good book on SQL syntax that will clear
thing up for me.
Other beginner resources:
1) http://www.sqlcourse.com/ http://www.sqlcourse.com/
2) http://sqlcourse2.com/ http://sqlcourse2.com/
3) http://www.freeprogrammingresources.com/sql.html
Thanks Umer Others,
You guys have been of great help.
Will try the query today..
Regards,
Mark
- Original Message -
From: Umer Farooq [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, October 11, 2004 8:17 PM
Subject: Re: SQL Query Help Please.
Here you.. go.. returns
2.) Can someone please suggest a good book on SQL syntax
Teach Yourself SQL in 10 Minutes by Ben Forta.
ISBN 0-672-32128-9
___
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com
[Todays Threads]
[This Message]
Hi,
http://www.techonthenet.com/access/queries/joins1.htm
For beginner I would suggest.. SAMS SQL in 21 Days..
and Google.. :-)
Nomad wrote:
Hello!
I am trying to create a join of four tables to get data from a db in the
format I want.
The Database tables and fields are:
Orders
Here's a start, though you'll have to explain your tables and your
calculations further.
SELECT
c.Customername,
a.Orderid,
a.Orderdate,
( where is this being stored? AmountReceived maybe this could be
d.PaymentReceivedAS AmountReceived ? ),
( put your formula for calculating AmountDue
Message -
From: Josh [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, October 11, 2004 6:07 PM
Subject: Re: SQL Query Help Please.
Here's a start, though you'll have to explain your tables and your
calculations further.
SELECT
c.Customername,
a.Orderid,
a.Orderdate,
( where
]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, October 11, 2004 6:07 PM
Subject: Re: SQL Query Help Please.
Here's a start, though you'll have to explain your tables and your
calculations further.
SELECT
c.Customername,
a.Orderid,
a.Orderdate,
( where is this being stored? AmountReceived
Here you.. go.. returns.. orderId, date, customerId, customer name,
paymentReceived, amount due (per order)... ,
SELECT orders.orderId, order.orderDate, orders.customerID,
customers.customerName, customerPayment.PaymentReceived
(SELECT sum(unitPrice * quantity)
FROM orderDetails
WHERE
this should work
SELECT ai.Product_ID, ai.Status
FROM Auction_Items ai, Auction_Bids ab
WHERE ai.Status = 'ACTIVE'
AND ai.date_EndDate #createODBCDate(now())#
AND ai.Product_ID = ab.Product_ID
GROUP BY ai.Product_ID, ai.Status
HAVING ai.Reserve_Price MAX(ab.Bid_Amount)
: RE: SQL Query Help (I'm stuck again)
Date: Wed, 10 Sep 2003 08:36:33 +0200
this should work
SELECT ai.Product_ID, ai.Status
FROM Auction_Items ai, Auction_Bids ab
WHERE ai.Status = 'ACTIVE'
AND ai.date_EndDate #createODBCDate(now())#
AND ai.Product_ID = ab.Product_ID
GROUP BY ai.Product_ID
: RE: SQL Query Help (I'm stuck again)
Using the following code, I get
cfquery name=Auction_GetAuctions datasource=#DatasourceName#
dbtype=ODBC
SELECT ai.Product_ID, ai.Status
FROM Auction_Items ai, Auction_Bids ab
WHERE ai.Status = 'ACTIVE'
AND ai.date_EndDate #createODBCDate(now
(ab.Bid_Amount)
/cfquery
basically this needs to pull all of the records that did not receive a bid meeting or
exceeding the reserve price.
Thanks Again
Original Message:
From: Michael Traher [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: SQL Query Help (I'm stuck again)
Date: Wed, 10 Sep
/2003 16:43
Aan: CF-Talk
CC:
Onderwerp: RE: SQL Query Help (I'm stuck again)
If I ad the Max(ab.Bid_Amount) to the select statment and remove the HAVING
line, I do not get any errors, but I also return all records that have had a bid
that worked, thank you very much.
So far out of all the resources I have used in the past, this one is by far the best.
Original Message:
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: SQL Query Help (I'm stuck again)
Date: Wed, 10 Sep 2003 16:51:14 +0200
You
(ab.Bid_Amount)
);
This statment does seem to pull the correct data.
Original Message:
From: Pascal Peters [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: RE: SQL Query Help (I'm stuck again)
Date: Wed, 10 Sep 2003 16:51:14 +0200
You need ai.Reserve_Price in the select
Use the cast function
WG
-Original Message-
From: Jeff Beer [mailto:[EMAIL PROTECTED]]
Sent: 30 January 2003 16:00
To: CF-Talk
Subject: OT: SQL query help
Using SQL Server, how would I define a query to return ranges of ID's
(primary key) where a tinyint field was not true?
That was a bit short !! [sic]
E.g.
select *
Where cast(column as bit) = 1
Wg
-Original Message-
From: webguy [mailto:[EMAIL PROTECTED]]
Sent: 30 January 2003 16:14
To: CF-Talk
Subject: RE: SQL query help
Use the cast function
WG
-Original Message-
From: Jeff
Short Version - I realize that is VERY long - but I jsut supplied alot
of information -
Temporary link online -
http://64.234.202.15/dbindex.cfm
LookUp existing tables - need to be altered on cfquery end efficiently -
8: within 2 -
8 in Table ApplicationModules = Medical Records
2 in Table
I don't fully undertand what you're doing. The first two queries, are those
examples of the data inside those tables, or are they views/queries of some
other table. It seems like all you need is a simple table join. Perhaps
you could send me the database and cfml off list and I could look at it
really obviously wrong.
-Original Message-
From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]]
Sent: 28 January 2002 17:25
To: CF-Talk
Subject: RE: SQL query help
Darren, since you are using an Access db go into your Access program.
Start
to create a query to return the value you want
Message-
From: Darren Adams [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 29, 2002 7:11 AM
To: CF-Talk
Subject: RE: SQL query help
cfquery name=browse datasource=marketing dbtype=ODBC
select * from main_table
where datestamp #dateformat(date,dd/mm/)#
/cfquery
This is really all I
is get out all the records that are later that
the
date the user specifys.
-Original Message-
From: Chris Sinkwitz [mailto:[EMAIL PROTECTED]]
Sent: 28 January 2002 16:12
To: CF-Talk
Subject: RE: SQL query help
What are you trying to do with your where clause?
-Original Message
What error do you get?
There are two major products that come out of Berkeley: LSD and [Unix]
BSD. We don't believe this to be a coincidence.
Doug Brown
- Original Message -
From: Darren Adams [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, January 28, 2002 7:40 AM
-
From: Darren Adams [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Monday, January 28, 2002 8:16 AM
Subject: RE: SQL query help
Well there are a number of options which users can search by.
All the other ones work but, when they enter a date I cant compare the
dates
and get it too
]]
Sent: 28 January 2002 16:44
To: CF-Talk
Subject: Re: SQL query help
What error do you get?
There are two major products that come out of Berkeley: LSD and [Unix]
BSD. We don't believe this to be a coincidence.
Doug Brown
- Original Message -
From: Darren Adams [EMAIL PROTECTED
Try reversing the mm/dd/yy into the db and see.
-Original Message-
From: Darren Adams [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 28, 2002 10:44 AM
To: CF-Talk
Subject: RE: SQL query help
Even though the datestamp field is a date/time field in access I still
get
this error message
I think it was just that id didnt like the value in quotes !!
-Original Message-
From: Michael Blair [mailto:[EMAIL PROTECTED]]
Sent: 28 January 2002 16:54
To: CF-Talk
Subject: RE: SQL query help
Try reversing the mm/dd/yy into the db and see.
-Original Message-
From: Darren
of
what
your SQL statement should look like.
-Original Message-
From: Darren Adams [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 28, 2002 9:56 AM
To: CF-Talk
Subject: RE: SQL query help
I think it was just that id didnt like the value in quotes !!
-Original Message-
From
Try
SELECT DISTINCT color
WHERE NAME = 'bar'
Steven Semrau
SRA International, Inc.
Senior Member, Professional Staff
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Com: (703) 805-1095
DSN: (703) 655-1095
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001
Chad,
Use the DISTINCT keyword:
SELECT DISTINCT
COLOR
FROM
Yourtable
..
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 4:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAME
You can either use the GROUP BY statement or do a
SELECT DISTINCT(Color)
FROM WHEREEVER
WHERE WHATEVER
hth,
Bill
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
do a group by query
SELECT *
FROM Bars
GROUP BY BarColor
hth
kevin
~
Kevin Mansel
Web Developer
Fox Communications
[EMAIL PROTECTED]
DL : 425-649-1321
C : 425-346-7221
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
Chad,
SELECT DISTINCT NAME ...
Ben
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAMECOLOR STYLE
1 bar blue12
2 bar blue
Chad,
OOPS! fumble-fingered today, meant
SELECT DISTINCT COLOR ...
sigh
Ben
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAMECOLOR STYLE
1
Chad, you'd want to do this:
SELECT DISTINCT COLOR
FROM tblWhatever
WHERE NAME = 'bar'
david
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAMECOLOR
SELECT DISTINCT color from tableName where name='bar'
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAMECOLOR STYLE
1 bar blue12
2
select DISTINCT color
from...
Tony
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 1:16 PM
To: CF-Talk
Subject: SQL query help
I have a database similar to this:
SKU NAMECOLOR STYLE
1 bar blue12
2 bar
SELECT
distinct(color) as color
WHERE
name = 'bar'
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-Original Message-
From: Chad Gray [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 08, 2001 4:16 PM
To: CF-Talk
Subject: SQL query help
I
PROTECTED]]
Sent: Tuesday, May 08, 2001 2:05 PM
To: CF-Talk
Subject: RE: SQL query help
do a group by query
SELECT *
FROM Bars
GROUP BY BarColor
hth
kevin
~
Kevin Mansel
Web Developer
Fox Communications
[EMAIL PROTECTED]
DL : 425-649-1321
C : 425-346-7221
-Original Message
Change the dates to a day earlier and a day later ?
Russell Brown
Internet Application Developer
Freeserve.com Plc, PO Box 452, Leeds LS2 7EY
Telephone: 0113 207 1203
-Original Message-
From: Edward Chanter [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2001 11:48
To: CF-Talk
Subject:
..
-= Ed
-Original Message-
From: Russell Brown [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 11:53 AM
To: CF-Talk
Subject: RE: SQL Query Help
Change the dates to a day earlier and a day later ?
Russell Brown
Internet Application Developer
Freeserve.com Plc, PO
I don't know if any of you know how to do it but I'm trying to get the SQL
query pasted below to include the two dates, at the moment it
doesn't return
data from the two dates specified. ie It returns queries starting
Jan 02 and ending 19 Feb
SELECTDateAdded, EmailA
.
-Original Message-
From: Edward Chanter [mailto:[EMAIL PROTECTED]]
Sent: 19 February 2001 12:04
To: CF-Talk
Subject: RE: SQL Query Help
I would do but the dates are input into a form by our client. They are
under the assumption that selecting the two dates will include them I'm
trying to find
Thanks very much Philip Putting the times in worked like a treat!
-= Ed
-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 19, 2001 12:06 PM
To: CF-Talk
Subject: RE: SQL Query Help
I don't know if any of you know how to do
60 matches
Mail list logo
