Hello all,

Have a question which I'm sure has a simple answer but my searching isn't finding what I need.

Taking orders for shirts from members of a club I belong to. I've had no problem writing a query that shows me the total number of shirts per size but can't quite get it right for total number of shirts (all sizes). Not all sizes have positive values (i.e. some are still 0).

The query I've tried is below. Each column keeps track of sizes small, medium, large, xl, 2xl and 3xl per member:

<cfquery name = "Oxford_Total" datasource="#request.dsn#">
SELECT SUM(O_S) + SUM(O_M) + SUM(O_L) + SUM(O_XL) + SUM(O_2X) + SUM(O_3X) AS Oxford_Total
FROM Shirt_Orders
</cfquery>

If I cut it down to just 2 sizes it works fine:

<cfquery name = "Oxford_Total" datasource="#request.dsn#">
SELECT SUM(O_S) + SUM(O_M) AS Oxford_Total
FROM Shirt_Orders
</cfquery>

I'm sure there's gotta be an easier way, but I'm still learning and hacking my way through things until I stumble on something that works. Digging through SQL & CF books I have, haven't helped.

Thanks for any help in advance,

Jim

try this out

<pre id=code><font face=courier size=2 id=code> SELECT (SUM(O_S) + SUM(O_M) + SUM(O_L) + SUM(O_XL) + SUM(O_2X) + SUM(O_3X)) AS Oxford_Total FROM Shirt_Orders </font id=code></pre id=code>

hope it helps.
Roddy

I forgot to mention. Make sure that all your fields are integer type else it won't work. Its not count where it counts the number of record in a tbale whereas sum counts the number found in each record.

Also when you do a sum you can work it out like this as well

select sum(field1 + field2 + field3 + field4) as Name

Regards,
Roddy

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I forgot to mention. Make sure that all your fields are integer type else it won't work. Its not count where it counts the number of record in a tbale whereas sum counts the number found in each record.

Also when you do a sum you can work it out like this as well

select sum(field1 + field2 + field3 + field4) as Name<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Hi Roddy....

- Checked the field types in my database, they were set by default to long integer. I changed them to integer.

- Tried both lines of code you suggested...neither are working. They're not generating any sort of error, just showing the total to be 0. I have individual queries for each shirt size that count the total per size as well, they all work fine. For instance, it shows I have orders for 1 large and 4 XL, so the total should show 5 instead of 0.

Any chance the code in my cfoutput tag is wrong?

&lt;cfoutput query="Oxford_Total"&gt;
#NumberFormat(Oxford_Total)#
&lt;/cfoutput&gt;



Edited by - seamusmcg on 19 Feb 2006 02:40:58

Are these O_S, O_M etc... fields found in 1 table itself???

Edited by - roders22 on 20 Feb 2006 16:42:42

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Are these O_S, O_M etc... fields found in 1 table itself???

Edited by - roders22 on 20 Feb 2006 16:42:42
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Yep...they're in a table called Shirt_Orders. The fields are for each size shirt, O_S is Oxford in small, O_M is Oxford in mediium, etc. (there are also fields names P_S, P_M for the polo style shirt).

If its all found in 1 table, and the field types are integer, There's shouldn't be any problem check you naming your connection string. I've tested all the sum function on my dummy table and so far if works fine. I don't c where the problem is on your. Are you using it as a query in an sql editor or are you using it with php or sumfin like that???

Just trying to use it as something of a report to keep track of shirt orders....on a cf page. Here's the exact code, copied out of homesite:

&lt;cfquery name = "Polo_Total" datasource="#request.dsn#"&gt;
SELECT SUM(P_S + P_M + P_L + P_XL + P_2X + P_3X) AS Polo_Total
FROM Shirt_Orders
&lt;/cfquery&gt;

&lt;cfquery name = "Oxford_Total" datasource="#request.dsn#"&gt;
SELECT (SUM(O_S) + SUM(O_M) + SUM(O_L) + SUM(O_XL) + SUM(O_2X) + SUM(O_3X)) AS Oxford_Total
FROM Shirt_Orders
&lt;/cfquery&gt;

As far as I can tell, it all looks ok and like I said, it doesn't generate any error but reports the total of each as zero. Some of those fields are zero and I'm wondering if that's screwing things up. I was looking in a SQL book and it says that columns with NULL values will be ignored by the SUM function. Maybe I need to go into my table and put a 0 in each empty record?

In which case you have to put a default value in you table. Normally int by default will automatically put a 0 there for you.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
In which case you have to put a default value in you table. Normally int by default will automatically put a 0 there for you.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
yep, it did.....problem was that I imported bunch of records in from another table and then added these shirt size fields afterwards, so they were left null. I just went in and manually put 0s in all the empty records.....that did the trick. Now the totals work! Thanks

I really need to study up on databases, especially relational databases because I have a bunch of redundant info (addresses, phone numbers, emails, etc) in multiple tables for the same people.

Make sure you set all your integer a default value of 0.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Make sure you set all your integer a default value of 0.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
they are....any new entries will have zeros entered by default. I took an existing membership list from another table and pasted those in the new shirts table. Then I added the new fields for shirt size and that was the problem apparently. It didn't put zeros in I guess because the fields were added to existing records.

Tried to save alot of time by putting in a record for each member ahead of time even if they'd not ordered a shirt. If I would have created the entire new shirt table first and then put in member info as orders were received, I'd have been better off since it would have entered zeros as defaults. Learning experience I guess.

Funny thing is, we did hats a couple years ago and now that I think about it, I had the same problem, just couldn't remember the "fix".