SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    plz help me in MySQL Query?????

    Tables are as follows..

    Clients
    `ClientID` int(11) NOT NULL auto_increment,
    `ClientName` varchar(50) collate latin1_general_ci default NULL,
    `ClientEmail` varchar(50) collate latin1_general_ci NOT NULL default '',
    `ClientPassword` varchar(10) collate latin1_general_ci NOT NULL default '',
    `ClientTelNumber` varchar(20) collate latin1_general_ci default NULL,
    `ClientCompanyInfo` text collate latin1_general_ci,
    `ClientCountry` varchar(50) collate latin1_general_ci default NULL,
    `ClientCity` varchar(50) collate latin1_general_ci default NULL,
    `ClientAddress` varchar(250) collate latin1_general_ci default NULL,

    SearchCodes
    `SearchCodeID` int(11) NOT NULL auto_increment,
    `SearchCode` varchar(25) NOT NULL default '',
    `ReportCode` varchar(100) default NULL,
    `ClientID` int(11) NOT NULL default '0',
    `Price` float(9,2) default NULL,

    Now I want to display those clients whose TotalPrice is greater than 1000;

    I wrote this query.

    select cl.ClientID, cl.ClientName, SUM(sc.Price) as Revenue from clients cl, searchcodes sc
    WHERE cl.ClientID=sc.ClientID
    GROUP BY cl.ClientID;

    This query return ClientID, ClientName and the total revenue generated by this customer. Now I want to place a condition "If Revenue is greater than 1000"

    [COLOR=SlateGray]
    Web Developer @ VeriQual

  2. #2
    SitePoint Addict
    Join Date
    Oct 2006
    Posts
    210
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After GROUP BY cl.ClientID add
    HAVING Revenue>1000;

    mikem

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thx buddy.
    its done!!!!
    [COLOR=SlateGray]
    Web Developer @ VeriQual


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •