MYSQL :: Calculating Totals From Two Tables

Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks.
I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it.

i am guessing that it has something to do with joins but mysql really isn't my thang!

I Have tried something like:

select
count(hotel.id) as atb,
count(trainer.rec_id) as det,
count(club.id) as ml,
count(activity.id) as sl
from
hotel, trainer, club, activity
but that returns the totalled amounts added together which is why I figure a join is needed.

My aim is a online stats panel something like:
Leisure Clubs Onsite: 2442
Trainers Onsite: 232
Hotels Onsite: 1978

View Replies !

Gathering Totals From Multiple Tables
I've got a little bit of an issue which I need a little bit of mysql-guru help with. I want to get the top 5 users who have authored the most articles, and how many articles each has authored (total). The problem is, I have 3 tables in which I store the articles. I've got articles_faq, articles_kb, and articles_ref.

Each of these has an auto_increment field 'id', and an 'author' field (there's obviously more fields, but they aren't relevant).

How would I get this data in a single query? Is it even possible using MySQL?

Although I'm improving my MySQL skills and knowledge quite a bit, this is beyond what I'm capable of, that's for sure.

View Replies !

Calculating Dates From Other Tables
below is some code we have created to enter in payment details for a customer....

INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable',
Date_Due = (select date_of_order from orders where order_no = (date_of_order+7));

I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...

View Replies !

Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement?
so far I have:

select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date)

but this doesn't include the 'zero' days (days when there were zero messages).

View Replies !

Totals/Mean Values
I assume this is a very simple question, I just don't know the answer!
I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc.
What I want to do is:
a) Total the price columns for each and take the average price - have all of this calculate automatically
b) Also call on these total values from an intro page displaying the totals for each year
First, is this possible?
Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic

View Replies !

Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish:
I want to be able to tell how many rows in a given table, and for a given INDEXED
column, carry any given ID.
Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose
one ID is: '56007'. I want to be able to tell how many rows in the entire table have
that ID. I know that I can use the select keyword, but i'm wondering if there's another
way, because the table i'll be doing this for can be up to 350 Million rows, and the
vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs.
I am only concerned with that small percentage of rows that have duplicate IDs

View Replies !

Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows:

field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40

how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table??

for above example, the output should look like:
field1 | field2 | field3 | field4 | field5
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
10 | 20 | 60 | 80 | 40
Total 50 | 100 | 300 | 400 | 200

View Replies !

Computing A Difference In Totals.
I am using php/MySQL 4.0 ..

If I had a table consisting of:

team l points
-------------------------------------
teamA l 15
teamA l 10
teamA l 5
teamB l 5
teamB l 10
teamC l 5

if (mysql_query("SET @rank = 0;", $conn))
{
if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn))

How would I get the result below?

rank l team l points l behind
--------------------------
1. teamA 30 0
2. teamB 15 15
3. teamC 5 25

I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?

View Replies !

Showing Totals And Subtotals In One Row
I have a table with the following fields:

ContractID | CustomerID | ProductID | Quantity

For each Contract there is one record: Who has ordered which product in what quantity.

Now I'd like to generate a report that shows:

- which products were ordered (SELECT ProductID ... GROUP BY ProductID)
- at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC)
- and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery?

Sample-Output:

P_ID - Quantity - Customer's quantities
----------------------------------------
1230 - 10'000 - A: 2'000, B: 8'000
1240 - 8'000 - A: 7'000, C: 500, D: 500
1120 - 6'000 - C: 6'000
...

How shall I build the SQL statement?

View Replies !

Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example:

select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC;

+-----------+------------+----------+
| a | b | Total |
+-----------+------------+----------+
| z | 2004-01-14 | 24 |
| x | 2004-01-05 | 22 |
| b | 2004-02-11 | 20 |
-------------------------------------

Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.

View Replies !

Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately.

So far the closest I've come is:

// to display the total for one day
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date='2007-05-25'

OR

// to display the total for all days
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date BETWEEN '2007-05-24' AND '2007-05-31'

With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.

View Replies !

Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query?

Example result for ProductX:

View Replies !

Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004.

With the following table structure:
TABLE_A
id
date
amount

My current set up is like this, based on calendar year:

PHP

// get a list of years in the db
$years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date))
FROM table_A r
ORDER BY YEAR(r.date) ASC");

foreach ($years as $s) {
    $yearSum = $db->getOne("SELECT SUM(r.amount)
    FROM table_A r
    WHERE YEAR(r.date) = '$s'");
    //echo something here
}

ISSUE1:
I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years.

ISSUE2:
I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.

View Replies !

Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes:

SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood
from survey where jobclass = 'Salaried' group by good

That returns this:

numgood | scoregood | totgood
27 | 4 | 108
70 | 5 | 350

What I need are results that include no votes cast for the other values:

numgood | scoregood | totgood
0 | 1 | 0
0 | 2 | 0
0 | 3 | 0
27 | 4 | 108
70 | 5 | 350

View Replies !

Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is:

- Specify a Date range
- Count number of rows in that range
- Get column totals for that range
- Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :)

This is a large table with many columns so here is what i had planned:

//OPEN CONNECTION HERE, SET DB
//First query gets date range:
$result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'");
//now we get number of rows:
$num_orders = mysql_num_rows($result);

After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?

View Replies !

Calculating Sum
Need help to calculate the total sum of an item sold on different days using SQL and display the result as Report to user using VB form

View Replies !

Calculating New Vs Old
How can I construct this query?

I have a bunch of values in a table. I want to output the % of values that only occur once to the % of values that occur more than once. In other words, if I have 1, 2, 2, 2, 3, 4, 4, I would like to output:

New numbers: 50%
Existing numbers: 50%

..new numbers because the 1 and 3 only occurred once and existing numbers of 2 and 4 because they occurred more than once. %s are %s of individual numbers.

View Replies !

Calculating Average Age
I got this players table and I do want to list its average age...

SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players

This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home.

View Replies !

Calculating A Percentage
I'm working on an adp file and I'm trying to do is calculate what percentage one figure is of another figure in a view.
In access I would simply do it like this:

[FirstField]/[SecondField]*100

But this doesn't seem to work. My exact code is as follows:

SELECT Product, Quarter, TotalPlanProductSpend, TotalPlanSpend, TotalPlanProductSpend / TotalPlanSpend * 100 AS PercentOfTotal
FROM dbo.Qry_RptPlanPRoductSetup

View Replies !

Calculating Median
I have to find out median amount of loan amount.
so can i do it in a query. I know its possible by writing function.
but still i am asking.I am new in MYSQL.I have searched for inbuild
functions.i didn't found any median function.

View Replies !

Errors Calculating Vat
Why do I get the wrong results with the code below.

iAmount = 16.84
iVatAmt = ((iAmount * 17.5) 100) (Returns 2.00 instead of 2.947)

iTotalAmt = iAmount + iVatAmt (Should = 19.79 but comes back with 18.84)

I know that the "" means interger division but if I use "/" I keep getting errors reported from PayPal that the Amount being passed to it is formatted incorrectly.

How do I calculate and add the vat element to the iAmount keeping the correct decimal format.

View Replies !

Calculating A New Field
Lets say I have the following price margins:
price------percent margin
between $1 and $5---- 60 - 80
between $20 and $40---- 50 - 60
The following works as expected but how do I accomplish the above?

select `c`.`eachprice` AS `eachprice`,`c`.`caseprice` AS `caseprice`,(`c`.`caseprice` - `c`.`eachprice`) AS `caseminuseach` from `UNFIW` `c` where ((`c`.`eachprice` between 12 and 24) or (`c`.`eachprice` between 1 and 4))

Something like this?
select `c`.`eachprice` AS `eachprice`,`c`.`sku` AS `sku`, if `c`.`eachprice` between 1 and 2.5 ((`c`.`eachprice`*.60)+`c`.`eachprice`) AS `custommargin` and if `c`.`eachprice` between 2.5 and 5 ((`c`.`eachprice`*.80)+`c`.`eachprice`) AS `custommargin` from `UNFIW` `c`

View Replies !

Calculating Time
I have a small application which sends out alerts at specified times during a day. I have a table with 3 fields :-

id int(11)
alertime datetime
sendbefore datetime

These alerts will get automatically sent out depending on how many hours the user specified in the sendbefore field. For example if the alertime is set to '04/02/2006 15:00:00' and sendbefore is set to '03/02/2006 15:00:00' then that alert needs to go out 24 hours before.

I need an SQL query or stored procedure which will return the alerts to be sent out during each hour of the day. And i am not sure how to do this. Hope it makes sense?

I dont mind making the sendbefore field into an int so that i only need to put in number of hours before the alerts should go out.

View Replies !

Calculating Moving Difference
I got 2 records by individual
with some fileds, one associates with the min date and the other one
assocaites with max date. So lay out looks as follows:

Key_ID | Dates_Min_Max Avg_Weight
1234 1/2/2004 12
1234 1/2/2006 24

I need to get the difference change between the weights for individual
ids that is group by Key_ID and find the percent change. Some thing
like this: (MAx_Row - Previos Row)/Previous Row * 100.

How to do this? How do I get the previos row between 2 records if I
order by the date?

View Replies !

Calculating The Sum Of Various Sections Of A Table
I have a table that has two columns. the first column has product names and the second has product quantities. The product names are similar to the example below.

01BlueChair
01BlueTable
01GreenChair
01GreenTable
01RedChair
01RedTable
02BlueChair
02BlueTable
02GreenChair
02GreenTable
02RedChair
02RedTable

Each product name has a number, a colour and an item type. What I need to do, is calculate the sum of the quantities for each number and product type. Eg, how many 01Chairs do we have? But I need to do that for 01Tables, 02Chairs and 02Tables as well, and output them in the same query results.

01Chair 12
01Table 15
02Chair 11
02Table 21

Some like the above. I can do it for one item, but not for all, for example here is the code I am using that outputs one result.

Code:

select
stock-code
sum(stk-trans-qty)

from
stock-movements
where
and stock-code like "01%Chair"

View Replies !

Calculating Business Days
I need to calculate the number of business days between a pair of arbitrary dates.Some scrounging around online delivered me this query,which basically does the trick:
Quote:SELECT
d1,
d2,
@dow1 := DAYOFWEEK(d1) AS dow1,
@dow2 := DAYOFWEEK(d2) AS dow2,
@days := DATEDIFF(d2,d1) AS Days,
@wknddays := 2 * FLOOR( @days / 7 ) +
if( @dow1 = 1 AND @dow2 > 1, 1,
if( @dow1 = 7 AND @dow2 = 1, 1,
if( @dow1 > 1 AND @dow1 > @dow2, 2,
if( @dow1 < 7 AND @dow2 = 7, 1, 0 )
)
)
) AS WkndDays,
@days - @wkndDays AS BizDays
FROM dates
ORDER BY d1,d2;

View Replies !

Calculating Time Difference
I have a task where I need to calculate the hours between two dates. However, they only want to calcute the time during Mon-Friday from 8-5. So if someone enters a question at 4:59PM on Friday and someone responds to them on Monday at 8:30. The user wants to see 31 minutes as the response time. I see that I have a function called dayname to get the Monday-Friday. However, I have no idea how I might actually use this to calculate the time lapsed. I need it to somehow ignore Saturday/Sunday.

View Replies !

Calculating Days Between Dates
Need to select records who's number of days between an expiration date and the current date that is between 0 and 90. I have a field in my MySQL database called "expire".

View Replies !

Calculating Difference Between Columns
__________________________________
| OPENED | CLOSED | RESOLVE TIME |
__________________________________

View Replies !

Calculating Table Usage
how posiible is it to calculate a table usage in byte.

View Replies !

Calculating Date Difference With PHP
I know nothing about MySQL (I'm more of a low level php guy). I'm building a reminder script that will remind the site admin to send out a newsletter. I want to send my reminder email email only if 30 days have passed since the last newsletter sending.

I've already setup a database that will be updated with the current date when a newsletter is sent out.

I found this function, which seams useful.

PHP

mysql> SELECT DATEDIFF(&#55614;&#57149;-12-31 23:59:59',&#55614;&#57149;-12-30');
-> 1

My guess is I could do something vaguelylike this:

PHP

$x = SELECT DATEDIFF(' CURRENT_DATE()',' the date in the dabase');
-> the difference between the 2 of them

From there I could use php to say if $x is greater than 29 days, send the email.

I was wondering if anyone could help me with the syntax / methodology of achieving this.

If MySQL is not the right method, please let me know. I was told that MySQL is the "best" way of doing this.

View Replies !

Calculating Innodb Space ?
I have just started with some innodb. So first I say there was a file called ibdata1 of size 10Mb. So as I add on data it became 18Mb now. So I want to find out is how to exactly know what is size of my innodb database.

View Replies !

Calculating Experience In Mysql
I had a workers table which contains their name and date of joining. Now i want to list the name, date of joining and calculate their experience in a separate field exp and the exp should be in an order.

View Replies !

Calculating Disk Space
I was wondering, is there a way to calculate the overhead in disk space for a table that contains column types that are only regular ints and floats. The table type is MyISAM, but I'd also like to know for other tables how to find this overhead. Basically I'm looking for a formula. If that's possible?

View Replies !

Calculating 'office' Or 'working' Hours
How can I calculate elapsed 'office hours' between dates?
e.g only counting time between 0900 and 1730 on weekdays, (and ideally excluding public holidays too, but that might be asking a bit much!)

View Replies !

Calculating Duration Between Specific Row Entries
Table:

CREATE TABLE `log` (
`id` int(11) NOT NULL auto_increment,
`session_key` int(11),
`date` datetime,
`level` varchar(30),
`action` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

- Every user has a unique 'session_key'
- Actions can in principle come in any order
- A user can have several 'game started' - 'game ended' pairs with the same session_key.

A typical log set of log-entries would look like:

1,1000, 2008-11-01 10:00:00,level2, game started
2,1001, 2008-11-01 10:01:00,level2, event3
3,1001, 2008-11-01 10:02:00,level2, event2
4,1002, 2008-11-01 10:02:30,level2, event2
5,1001, 2008-11-01 10:03:00,level2, game started
6,1001, 2008-11-01 10:04:00,level2, game ended
7,1000, 2008-11-01 10:05:00,level2, event3
8,1000, 2008-11-01 10:06:00,level2, game ended
9,1000, 2008-11-01 10:07:00,level2, game started
10,1000, 2008-11-01 10:08:00,level2, game ended

The query should give the following result
session_key, level, duration (hh:mm:ss)
--------------------------------------
1000,level2,00:06:00
1001,level2,00:01:00
1000,level2,00:01:00

View Replies !

Calculating A % Based On Date Range
I have a table called TransResult and I have 2 data fields in it. I want to get the percentages for the 2 possiable values in TransResult and I want this done by date, so I can go backwards to compare what was done in the past months to what is done in current month.

This is what i have come up with so far, however my _total is giving me ALL records in the table not this months records, so my percentages are off, aside from the _total the values it is calculating are correct.

SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) +0 GROUP BY transresult

View Replies !

Calculating Days To Excluding Weekends
I have a table(t2) with the following fields

t2
Fields Datatypes
StartDate datetime
EndDate datetime
#_of_days_Taken decimal

A user will select start date and end date , So I want the system to automatically update #_of_days_Taken but it needs to excludes weekends only.

I currenntly have it like this --> #_of_days_Taken =(EndDate - StartDate)

If anyone knows how to do it please show me how to go about it.

View Replies !

Calculating And Displating Voting Results
The results pages of myverdict.net have been the most difficult so far. It took me a month of research to find out how to write the sql query. I eventually found the answer in an added comment in mysql documentation. Here was my problem. I had a table with a vote column, the entries for which could be For, Against or Undecided. Counting the total votes was easy enough and by grouping I could return a count for each. However, I wanted to display results for every question in a particular category, on one page, using a repeating table, the results reading across the page. A repeating region only displays one row at a time and my simple grouped query would not do as it returned multiple rows.
Here then, for the sql buffs out there is the query that worked.

SELECT questions.question, COUNT(votes.vote) AS total,
COUNT(votes.vote = ‘For’ OR NULL) AS col1,
COUNT(votes.vote = ‘Against’ OR NULL) AS col2,
COUNT(votes.vote = ‘Undecided’ OR NULL) AS col3
FROM questions, votes
WHERE questions.questionID = votes.questionID
GROUP BY questions.question

You apparently need the ‘OR NULL’ or else it doesn’t work, I don’t know why. Anyway it was a simple matter to display col1, col2, col3 and total votes in the repeating region of my page.

View Replies !

Calculating Unused Time, Per Day Given Date Ranges
I've found a few potential solutions to this, but mostly they use Oracle Analytics syntax or SQL server specific extensions. I'm struggling to come up with something that works in Mysql.

I've got a table of date ranges, let's call it "bookings"

Code:

roomid | startdate | enddate
-------+------------------+-----------------
1 | 2008-02-03 13:00 | 2008-02-03 17:00
1 | 2008-02-03 18:00 | 2008-02-03 19:00

I'm trying to come up with a query that will give me the number of unused minutes for a room for a given day, such as:

Code:

roomid | date | unused
-------+------------+-------
1 | 2008-02-02 | 1440
1 | 2008-02-03 | 1140
1 | 2008-02-04 | 1440......

View Replies !

Trouble Calculating Percentages For A Unique Data Set
I'm having a hard time calculating the desired percentages for a weather database. Here is a sample of the database: ....

View Replies !

Using Only Weekdays And Excluding Weekends When Calculating Dates
I need to figure out how many days are between certain dates excluding weekends. Is it possible to do this. I have tried searching google, but I guess I am not using the right keywords because the results I am getting back aren't giving me much help.

I was thinking about setting up a table and putting in all weekend dates and then pulling in that data, but thought there might be an easier way.

View Replies !

Calculating Distance With Latitude/longitude In MySQL
I was trawling around the web and discovered the following gode on a website called http://ben.milleare.com and Ben is an english guy who has produced the following MySQL snippet which calculates the distance between two sets of Long Lat points.

SELECT id,name,(((acos(sin(($lat*pi()/180))
* sin((latitude*pi()/180))
+ cos(($lat*pi()/180)) * cos((latitude*pi()/180))
* cos((($lng - longitude)*pi()/180))))*180/pi())*60*1.1515)
as distance FROM companies
HAVING distance <= $miles
ORDER BY distance ASC LIMIT xx
$lat and $lng for the starting point as well as $miles for the max distance to search

This works excellently.

However what i want to do is twist this slightly to take a table where the users (whose home postcodes have been converted to Long Lat points ~ possibly by google) will have a MaxDistance they are prepared to travel to a venue for an event.

I have changed the above statement to:

SELECT id,user,longitude,latitude,
(((acos(sin((51.75733*pi()/180)) * sin((latitude*pi()/180))
+ cos((51.75733*pi()/180))
* cos((latitude*pi()/180))
* cos(((-0.341325 - longitude)*pi()/180))))*180/pi())*60*1.1515)
as distance FROM tblusers
HAVING distance <= tblusers.Maxdistance
ORDER BY distance ASC LIMIT 5
the values 51.75733, and -0.341325 are the Long and Lat co-ordinates for an event location and the tblusers.Maxdistance is the column in TABLE tblusers which holds the max distance that they are willing to travel.

This doesnt work.

I am a noob to MySQL and therefore not sure if what i am trying to do is possible or not or if it is something that just needs the aplications of another clause.

I have tried WHERE instead of HAVING but this the errors with 'distance' not being a valid column....

View Replies !

How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do:

1- I have Two tables: polls_created and votes

2- Table polls_created is like:

poll_id
owner
poll_subject

3- Table votes has the votes issued for a given poll, like this:

vote_id
poll_id
vote
vote_date

So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables.

How do I then generate this result:

MySQL Code:
SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes
"sorted by polls that have gotten most number of Votes"

Of course "sorted by polls that have gotten most number of Votes" is not real MySQL

View Replies !

Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction.

Sound doable?

MySQL 4.1

Data looks like this:

user, amount, date
==============
1, 50, 2003-11-23
2, 34, 2004-10-04
3, 45, 2005-08-30
3, 98, 2006-04-02
3, 76, 2000-02-03
2, 91, 2000-12-04
1, 11, 2003-11-05
3, 22, 2003-03-06
4, 34, 2006-03-07
5, 45, 2006-06-24

I figure I can group by userID but how do I get the date of the most current transaction?

Using the data above, the query would return:
1 (user) 61 (subtotal) 2003-11-23 (last transaction)
2 (user) 125 (subtotal) 2004-10-04 (last transaction)

View Replies !

"Totals" Row For Columns
Looking for the result below from the sample table listed. Using MySQL 4.0/php. ROLLUP is not supported in my version of MySQL ...

table1
--------------------------
Person l Units l Amount
--------------------------
person1 l 7 l 11
person1 l 8 l 11
person2 l 13 l 6
person2 l 13 l 7
person3 l 11 l 7

SELECT Person, SUM(Units), SUM(Amount) FROM table1 GROUP BY Person

person1 l 15 l 22
person2 l 26 l 13
person3 l 11 l 7
"totals: l 52 l 42" = desired result

View Replies !