adding up data and numbering table rows?

question 1. I have a repeating table. One column should show what number row it is. AllI have now is each row has a 1 as its number. I would like each row to be numbered individually.

Question 2. in my database table each record has a column for month day and year as well as a column that holds a number (0, 1, 0r 2). Then I have table that I would like to add up all of the numbers for a particular date. If the recordset returns info for 10/12/09 then i would like for it to add all the numbers in that column. Lets say there are 3 entries for 10/12/09, each entry has a 2 in its number column. the table cell should display a 6.

Thanks dean

Dean,

For question 1, you could do one of two things... One way would be to use PHP's/MySQL's Row Count features.

But probably easier is this: before the loop of the table establish a variable like this <?php $rowcount = 0; ?>.

Then inside the looped table, create your first column where you will want to denote the row number and in the cell, call this command <?php echo ++$rowcount; ?>.

You will then have your rows numbered on up! I look at question 2 in a bit... I gotta get some sleep.

Cheers,

Brian

SOJO Web has question 1 covered.

For question 2, use the SUM() function for the number column and GROUP BY the date column:

SELECT SUM(numberColumn) AS numCount FROM tblName GROUP BY dateColumn

Thanks guys,

Took care of the first on pretty easily. However the 2nd question has got me. Jason my date column is actually4 columns month, day, year and ampm. So I'm not sure how to fit this into your answer.

This is what I have so far (stop laughing), it obviously does nothing.

SELECT SUM ( tripmanifest.Air) AS numCount
FROM tripmanifest GROUP BY tripmanifest.`Month`

Dean

Well, that's a horse of a different color then, isn't it? ;)

No worries, just need to add the CONCAT() function to the GROUP BY:

SELECT SUM(numColumn) as numCount FROM tbleName GROUP BY CONCAT(monthColumn, dayColumn, yearColumn, ampmColumn)

Jason,

This is working sort of. This is what I have:

SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY CONCAT (Month,Day,Year,ampm)

and it doesn't work. If I change it to:

SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY 'Month', 'Day', 'Year', 'ampm'

It totals ALL the numbers but not sorted by date. If I put the CONCAT() in ( like below)then it is deleted when I save the recordset.

SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY CONCAT('Month', 'Day', 'Year', 'ampm' )

...Something just hit me, How does the recordset know which Month, Day, Year, ampm to sort by? In the url to this page I am passing that info. Does that need to be included in the recordset?

Dean

Month, Year, Day and ampm are not MySQL reserved words, so there is no need to enclose them in single quotes.


writing the query without the quotes:
SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY CONCAT(Month, Day, Year, ampm)

should work just fine.
MySQL does not like the use of the standard single quote character " ' ", instead, you should use the backtick "`"


if you are going to use quotes in the SQL, you need to use the backtick instead of the single quote:
SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY CONCAT(`Month`, `Day`, `Year`, `ampm`)

I put ecaxtly what you said but as soon as I save the recordset it removes the CONCAT(). like this:

SELECT SUM(Air) AS numCount
FROM tripmanifest
GROUP BY Month, Day, Year, ampm

And its not totaling the numbers. heres the file.

inthe code you'll see I added the CONCAT() but still nothing and it still does not show up in the recordset.


Dean

I am not able to recreate this behavior in Dreamweaver.


When I save a recordset that uses the concat function in the group by clause, the concat function is not removed.


Try editing the code directly.

change line 83:

$query_RsAM = "SELECT SUM(Air) AS numCount FROM tripmanifest GROUP BY Month, Day, Year, ampm";

to:

$query_RsAM = "SELECT SUM(Air) AS numCount FROM tripmanifest GROUP BY CONCAT(Month, Day, Year, ampm)";

Jason thanks for the quick reply,

Still no go it shows 0 as the total.

How does the recordset know which date to look at?

heres the up dated file.

Dean

ps.

Ok I tried something that may or may not tellyou something. I put 1 in each row under the Air column. There are about 65 or so rows only 5 of which have the same date(month,day,year.ampm columns). the numCount binding is displaying 40 as the total. My inferior brain is still saying how does it know which date?

Dean