Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005
    Posts
    1

    Unanswered: Postgresql query problem, desperately need help

    Hello,

    I'm having a problem with a query I am writing. The query is below:

    Code:
    SELECT
    	"Campaign"."ID",
    	"Campaign"."name",
    	COUNT("CampaignTransactionView"."ID") AS "viewCount",
    	SUM("CampaignTransactionView"."subTotal") AS "viewCommission",
    	COUNT("CampaignTransactionClick"."ID") AS "clickCount",
    	SUM("CampaignTransactionClick"."subTotal") AS "clickCommission",
    	COUNT("CampaignTransactionLead"."ID") AS "leadCount",
    	SUM("CampaignTransactionLead"."subTotal") AS "leadCommission",
    	COUNT("CampaignTransactionSale"."ID") AS "saleCount",
    	SUM("CampaignTransactionSale"."subTotal") AS "saleCommission"
    FROM "Campaign"
    LEFT JOIN "CampaignMaterial" ON ("CampaignMaterial"."campaignID" = "Campaign"."ID")
    LEFT JOIN "CampaignTransactionView" ON ("CampaignTransactionView"."campaignMaterialID" = "CampaignMaterial"."ID")
    LEFT JOIN "CampaignTransactionClick" ON ("CampaignTransactionClick"."campaignMaterialID" = "CampaignMaterial"."ID")
    LEFT JOIN "CampaignTransactionLead" ON ("CampaignTransactionLead"."campaignMaterialID" = "CampaignMaterial"."ID")
    LEFT JOIN "CampaignTransactionSale" ON ("CampaignTransactionSale"."campaignMaterialID" = "CampaignMaterial"."ID")
    WHERE "Campaign"."campaignStatusID" = 2
    GROUP BY "Campaign"."ID", "Campaign"."name"
    ORDER BY "Campaign"."name" ASC, "Campaign"."ID" ASC
    Tables CampaignTransactionView, CampaignTransactionClick, CampaignTransactionLead and CampaignTransactionSale are inhiritted (oo) from the table CampaignTransaction. CampaignTransactionClick has 27 records in it, CampaignTransactionView 182, CampaignTransactionLead 0 and CampaignTransactionSale 0.
    All records in CampaignTransactionView have a subTotal of 0.000250, so that makes 0.0455 in total (182 * 0.00025). The records in CampaignTransactionClick have a subTotal of 0.15, so total should be 4.05 (27 * 0.15).
    Table output should be:

    Campaign Views Comm. Clicks Comm. Leads Comm. Sales Comm.
    Wehkamp 182 0.05 27 4.05 0 0,00 0 0,00

    But the query results in:

    Campaign Views Comm. Clicks Comm. Leads Comm. Sales Comm.
    Wehkamp 4.941 5,28 4.941 741,15 0 0,00 0 0,00

    I already tried other types of joins (FULL etc), but that doesn't seems to do the trick. The strange thing is that the query does work when only joining CampaignTransactionView or CampaignTransactionClick etc, but using all four together mixes up values.

    I desperately need help on this, because what i'm making should be finished next week. This is one of the last queries i'm having problems with.

    If someone can tell me what I'm doing wrong, I would be amazingly thankful.

    Thanks in advance!

    Tim

  2. #2
    Join Date
    Mar 2005
    Posts
    1

    Nobody help him

    Nobody should help him he doesnt help the people who buy his bug infested scripts.

    Brian
    rementer
    itpaystogolf.com

Posting Permissions

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