SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select multiple results

    Hi all,

    Below query records multiple time:

    Two tables: erm_insurance, erm_receipt

    SELECT DATE_FORMAT( FROM_UNIXTIME( a.receipt_date ) , '%M' ) AS
    MONTH , if( DATE_FORMAT( FROM_UNIXTIME( a.receipt_date ) , '%Y' ) = '2007', sum( insurance_premium ) , '0' ) AS '2007', if( DATE_FORMAT( FROM_UNIXTIME( a.receipt_date ) , '%Y' ) = '2008', sum( insurance_premium ) , '0' ) AS '2008'
    FROM erm_insurance as b
    JOIN erm_receipt as b ON b.insurance_id = a.insurance_id
    GROUP BY DATE_FORMAT( FROM_UNIXTIME( a.receipt_date ) , '%M' ) , DATE_FORMAT( FROM_UNIXTIME( a.receipt_date ) , '%Y' )

    Output:
    MONTH 2007 2008
    December 0 145000.00
    November 0 308200.00
    October 7000.00 0
    October 0 507800.00

    Expected output:
    MONTH 2007 2008
    December 0 145000.00
    November 0 308200.00
    October 7000.00 507800.00

    If you know Expected output result queries replay immediately

    Advance Thanks
    By
    Visva

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Remove the second expression from the group by clause.

  3. #3
    SitePoint Member
    Join Date
    Dec 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi All,

    I got the answer from this query, what i thought .

    SELECT DATE_FORMAT( FROM_UNIXTIME( erm_receipt.receipt_date ) , '%M' ) AS MONTH ,
    sum(CASE WHEN DATE_FORMAT( FROM_UNIXTIME( erm_receipt.receipt_date ),'%Y')='".(CHART_YEAR-1)."' THEN erm_insurance.insurance_premium ELSE 0 END) as '".(CHART_YEAR-1)."',
    sum(CASE WHEN DATE_FORMAT( FROM_UNIXTIME( erm_receipt.receipt_date ),'%Y')='".CHART_YEAR."' THEN erm_insurance.insurance_premium ELSE 0 END) as '".CHART_YEAR."'
    FROM erm_insurance
    JOIN erm_receipt ON erm_insurance.insurance_id = erm_receipt.insurance_id
    GROUP BY DATE_FORMAT( FROM_UNIXTIME( erm_receipt.receipt_date ) , '%M' )

    Thanks for your reply
    Last edited by arvisva16; Jan 1, 2009 at 23:15. Reason: Content modifed


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
  •