MYSQL syntax

I have the following statement to extract data from the last 5 years of a table:

SELECT
`invoice`.`Inv_Num`,
`invoice`.`Cust_Num`,
`invoice`.`Inv_Date`,
`invoice`.`Vat_Rate`,
`inv_list`.`Line_ID`,
`inv_list`.`Quantity`,
`inv_list`.`Sell_price`,
`inv_list`.`Line_Total`,
`inv_list`.`Description`
FROM
`invoice`
Inner Join `inv_list` ON `invoice`.`Inv_Num` = `inv_list`.`Inv_Num`
WHERE
`inv_list`.`Description` LIKE '%parking charge%' AND
`invoice`.`Inv_Num` > '670'

I need to extract each months data and sum the line total. Is there a simple way to do this?

Cheers

SteveW
0
Steve
1/12/2009 9:03:55 PM
embarcadero.delphi.database 1294 articles. 0 followers. Follow

1 Replies
666 Views

Similar Articles

[PageSpeed] 38

> {quote:title=Steve Warburton wrote:}{quote}
> I have the following statement to extract data from the last 5 years of a table:
> 
> SELECT
> `invoice`.`Inv_Num`,
> `invoice`.`Cust_Num`,
> `invoice`.`Inv_Date`,
> `invoice`.`Vat_Rate`,
> `inv_list`.`Line_ID`,
> `inv_list`.`Quantity`,
> `inv_list`.`Sell_price`,
> `inv_list`.`Line_Total`,
> `inv_list`.`Description`
> FROM
> `invoice`
> Inner Join `inv_list` ON `invoice`.`Inv_Num` = `inv_list`.`Inv_Num`
> WHERE
> `inv_list`.`Description` LIKE '%parking charge%' AND
> `invoice`.`Inv_Num` > '670'
> 
> I need to extract each months data and sum the line total. Is there a simple way to do this?
> 
> Cheers
> 
> SteveW

Using Delphi???

When I have to do that I always use a ClientDataSet.

1 InternalCalcField + 1 Aggregate Field will do the job. And as a bonus: You get RDBMS flavor independance.

Best regards
0
Alexandre
1/17/2009 12:17:44 AM
Reply:

Similar Artilces:

How to attach a MySql database to a MySql database server.
Hi all, I have developed a web application in VS 2005, and it uses MySql 4.1 as database. Now, I have created the database and tables, and there is data in it. How can I copy this same database along with its tables and contents, to another server? Which all files should I copy? Is there any procedure to 'attach' a database? I use MySql  Manager to create databases and tables. Thanks Tomy Hi The best way to make a dump of your existsing MySql database is to use mysqldump utility which comes with any mysql server package. Here is a sample usage: "C:\Program File...

Create a MySQL database from another MySQL database or Backup
I'm starting on a new ASP.NET project using MySQL as backend. While I'm familiar with SQL2k, I'm totally new in MySQL. Here's my questions: 1) Given a Web Server for installing the MySQL database, what do I need to start off? MyODBC and/or some other mySQL connector or console? I read tutorial mention Telnet or Shell access. What's that? I know how to create a FTP database in SQL2k. How's that done in MySQL? 2) Also given a backup of a MySQL database as data source and a remote MySQL database the backup was made from, what are the technical steps to create my first MySQL dat...

Backup a MySQL database from Delphi
Is there an easy way to backup/restore a MySQL tables from my Delphi application. The database is hosted by my Web host. To remotely connect to my MySQL database, I use ADO (dbGo tab) components. My tables use MyISAM. Can I use mysqldump or mysqlhotcopy from Delphi via ADO? BTW, I use Delphi 2009. > Can I use mysqldump or mysqlhotcopy from Delphi via ADO? mysqldump does not need any additional connectivity layer, like ADO, ODBC or what else. mysqldump uses TCP/IP to connect to MySQL server. So, all what you need: - mysqldump of the your MySQL server version; - run mysqldum...

How to convert the database in a mysql database ???
I need help, i want to put asp.net portal to a mysql data base Good luck! Since the Data MAnipulation Layer is written in Stored Procedures, you will have to rewrite all of it. It probably would not be very time effective in my opinion. Well, maybe it won't be that bad. Looks like someone is already doing it. Check out their forum for more upates. http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=482434...

mysql database, delphi 2010 and connection charset
Hi. I want to write an application which will use a MySQL database. I made a basci connection and queries. Everything is ok, except viewing data from database on a website generated with php. I'm polish and using my national letters and this is problem. When i add text into database in my app written in delphi and then view it in app, everything is ok, national letter are correct. But, when i want to view this data on a website i see strange things where were national letters. Fields in database are defined i n UTF-8, website uses UTF too. I can see on website data added from delphi on...

Generation of MySQL database uses incorrect escape syntax
Hi, I was wondering how to change the 'escape' character from " (double quote) to ` (backtick). MySQL doesn't understand the double quote. For example: When generating a table named 'table' it generates "table" not `table`. I couldnt find the setting in the language defenition. However, i know that i can enable ANSI_QUOTES in MySQL but that doesn't solve the problem on the powerdesigner side. Regards, Elger Jonker In PD12.1 you go to Database >> Generate Database >> Format tab >> change the Identifier delimi...

SqlConnection not connect to database in Delphi XE 2(mysql).
SqlConnection not connect to database in Delphi XE 2(mysql). Note: The machine was installed with Delphi 2007. error: First chance exception at $7576D36F. Exception class TDBXError with message 'DBX Error: Driver could not be properly initialized. Client library may be missing, not installed properly, of the wrong version, or the driver may be missing from the system path.'. Process Project6.exe (5732) Help? Aldnet Brasil wrote: > SqlConnection not connect to database in Delphi XE 2(mysql). > Note: The machine was installed with Delphi 2007. > error: > First...

To change database from Acess into MySQL database
I want to change my database from Access to MYSQL . I can connect Ms Access database using the following syntax. Please let me know how I can connect mysql database same as that of access connectivity as given below [database] DBMS=ODBC DBParm = ConnectString='Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\testdata\employee.mdb;Uid=;Pwd=' AutoCommit=FALSE with thanks Polachan ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp This article should help: http://pbdj.sys-con.com/node/42600 You'll need to down...

Converting SQL syntax to MySQL syntax
 Hi, the following is a stored procedure written for SQL,  would this work in MySQL or do I have to change the syntax? If so would you please point me to the site that will show me how to convert it to one that is equivalent to the MySQL syntax?  Thank you in advance for your help.CREATE PROCEDURE sp_PagedItems    (     @Page int,     @RecsPerPage int    )AS-- We don't want to return the # of rows inserted-- into our temporary table, so turn NOCOUNT ONSET NOCOUNT ON--Create a temporary tableCREATE TABLE ...

MYSQL database server vs. MaxDB by MYSQL
Which one should I choose? I am starting a project in tight budget, and I think I will use MYSQL instead of SQL server for this one. This project will be in heavy load. (for commercial use) I am wondering if I should use the standard MySql or the MaxDB? I have no idea because they both are free or low in license cost if I have to pay. I read about the stuff on the mysql.com, it says only about 5000 clients are using MaxDB. is that a reason for that? or MaxDB only good for SAP? Please advice. I am currently using MaxDB and developing asp pages against it. Works great fo...

MySQL DataTransfer from one database to another database
Hi , I've two database in mysql and I want to transfer data from my database to another database.   what is the System to do so. http://www.Net4developers.com http://www.AjaxSpider.com    A k Vermahttp://www.Net4Developers.com If you couldn't just transfer database files, you can script your database (script table definitions and table data) and run SQL script on your target server....

mysql: how do i go about retrieving data from a mysql database?
Hi all,Using vb.net, whats the best way to get data from a mysql database? I want to write a class that does this for me http://www.mysql.com/products/connector/net/ Download MySQL connector from above link, reference it in your project and put it in the Bin folder of your project. Make a connection string in your web.config, and build a data class and a data access class in your preferred language. The data class being a representation of your table, and the data access class recieving the data object as a parameter to its insert, delete and update functions. Then link an objectdatasour...

convertion of SQL Server database to mysql database
Hello!! I am interested on the convertion of SQL Server database and all its objects to mysql to get the portal working using mysql as a back end. If anyone coul help me, please reply and send me the DDL.   thanks for all...

copying a table from mysql database to sqllite database
hi everyone !!i am selecting a table from the tblusers from mysql database.and i want that when my aspx page loads itself then the table tblusers gets copied into the sqllite database.  for which i have to connect to both the databse.please suggest some idea and queries how can i do this? waiting for your replies Thanks in advance.    ...

Web resources about - MYSQL syntax - embarcadero.delphi.database

Syntax - Wikipedia, the free encyclopedia
In addition to referring to the discipline, the term syntax is also used to refer directly to the rules and principles that govern the sentence ...

A Better Syntax for Scheduled Tasks
... in progress, if you'd like to know when it's ready, follow me on Twitter ... where I almost never tweet anything). Schyntax Syntax In contrast ...

Syntax City on the App Store on iTunes
Get Syntax City on the App Store. See screenshots and ratings, and read customer reviews.

The Syntax Of Psychedelic Time (Terence McKenna) [FULL] - YouTube
http://alchemicalarchives.blogspot.com/ Terence McKenna, speaking in Berkeley, California in July of 1983. Terence McKenna talks about the I ...

Nick Cummins bringing his unique sense of style, and syntax, to Japanese Rugby
THE eyes of Australian rugby fans will be trained on Eden Park this weekend as the Wallabies look to make history against the All Blacks.

Writer Pro Out Now For iOS And Mac: iA Writer Goes Pro With Syntax Control And More
... you can’t see the video embedded above, please click here . In addition to its simple workflow, Writer Pro boasts a new feature called Syntax ...

WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight
Holen Sie sich „WordEver HD - Text Editor with Redesigned Keyboard and MarkDown syntax & formatting highlight“ im App Store. Sehen Sie sich Screenshots, ...

Mastering Windows Search using Advanced Query Syntax
Search is a pervasive feature of Windows 7, but few really know how to use it …

Ten Things You Should Know About Haskell Syntax
I have posted a new blog about Haskell that I think may be read and understood by any curious programmers. It’s about Haskell syntax, which seems ...

Syntax era: Sir Clive Sinclair's ZX Spectrum revolution
Sir Clive Sinclair discusses the long-term impact of his Spectrum computers and why their successors are likely to wipe out humankind.

Resources last updated: 12/19/2015 10:40:15 AM