SQL help please

Try

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) 'Raised Mldg/Lvr MDF' as price " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"

Big Monty... the issue is in the recordset loop:

while not rs.eof
      response.write "<tr>"
      response.write "<td>" & rs.fields("fprodcl") & "</td>"
      response.write "<td align=right>" & formatnumber(rs.fields("amount")) & "</td>"
      response.write "</tr>"
      rs.movenext
wend

It never references 'price' or 'Raised Mldg/Lvr MDF'... just the fields fprodcl and amount

I made the assumption that you are having trouble accessing the field name, that's why I modified the SQL in added an alias for that call. Now you can access the field in your Loop by using the column named price (you can call it anything)

Can you modify this loop to include price?

while not rs.eof
      response.write "<tr>"
      response.write "<td>" & rs.fields("fprodcl") & "</td>"
      response.write "<td align=right>" & formatnumber(rs.fields("amount")) & "</td>"
      response.write "</tr>"
      rs.movenext
wend

Is the db MySQL?
Yes, then try backticks after the SUM() instead of apostrophes:

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) as `Raised Mldg/Lvr MDF` " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"

Select allOpen in new window

If it is MSSQL or MSAccess try brackets intead:

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) as [Raised Mldg/Lvr MDF] " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"

Select allOpen in new window

Then use:

 response.write "<td align=right>" & rs.fields("Raised Mldg/Lvr MDF") & "</td>"

Select allOpen in new window

Still doesn't work.  I now changed SQL to:

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) price " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"


and loop to:
while not rs.eof
      response.write "<tr>"
      response.write "<td>" & rs.fields("fprodcl") & "</td>"
      response.write "<td align=right>" & formatnumber(rs.fields("price")) & "</td>"
      tempTotal = tempTotal + rs.fields("amount")
      response.write "</tr>"
      rs.movenext
wend

It now bulks at formatnumber.  Remove formatnumber and it doesn't sum.  I think this can't be done in asp.

Let's pretend there are two fields... one called Letter and one called number.  The data looks like:

a   1
b   1
c    1
d    1

I want the output to be

a  1
bandc 2
d  1

Shouldn't I be able to use a "sum (case when letter in ('b', 'c') then number) as something" to sum b and c?  If so then how do I spit out the recordset?

Shouldn't I be able to use a "sum (case when letter in ('b', 'c') then number) as something" to sum b and c?

what's your backend database?

and can you post some sample data for tables below?

• aritem
• armast

Let's pretend that a table contains:

Cats      12
Dogs      13
Dogs      8
Horses      5
Cows      8
Cows      3
Bulls      5
Bulls      3

Is there a way to run a query that would spit out the following?

Domestic      33
Horses      5
Cows      11
Bulls      8

Basically is sums everything up but puts cats and dogs into a Domestic category?

This is the essence of my problem.

How would you loop through and display the results?

Basically is sums everything up but puts cats and dogs into a Domestic category?

yes, definitely this is feasible.

whether:

1. you do a grouping in your SQL select statement
2. or you have another mapping table to map your values with category.

it depends on which approach you want to go with.

Still doesn't work.  I now changed SQL to:

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) price " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"

this didn't work because you're missing the word "as":

SQL = "Select " & _
      "sum(ftotprice) as amount, fprodcl, " & _
      "sum(case when fprodcl in ('SR','LV','SD') then ftotprice end) as price " & _
      "from aritem " & _
      "left outer join " & _
      "armast on armast.fcinvoice = aritem.fcinvoice " & _
      "where finvdate = '" & request.form("theDate") & "' " & _
      "group by fprodcl"

Ok... so how do you display the resulting recordset?  I'm using ADO.

Remember, we want it to just show the product and quantity... BUT, it should accumulate the Cats and Dogs under Domestic.

while not rs.eof

  ??

  rs.movenext

wend

personally i would go with solution >> 2. or you have another mapping table to map your values with category.

but we need to know what's your backend database.

Backend is SQL 2000