Query with joins, count()s and sum() producing odd results

bunner_bob

Well, probably not "odd" - much more likely bad SQL on my part.

I'm attempting to get a list of all customers, and get each customer's count of orders from the order table, count of cart items from the cart items table, and sum of store credit from the credits table.

=====================================

SELECT c.*, SUM(sc.amount) AS creditTotal, COUNT(o.orderId) AS orders, MAX(o.orderDate) AS lastOrder FROM customers c LEFT JOIN storeCredits sc ON c.customerId=sc.customerId LEFT JOIN orders o ON c.customerId=o.customerId GROUP BY c.customerId ORDER BY c.lastName ASC

This gets me an "orders" count of 4 for my second customer, which is correct. But when I join cartItems to the query I get an orders count of 8, and a cartItemCount of 8 (should be 2).

SELECT c.*, SUM(sc.amount) AS creditTotal, COUNT(o.orderId) AS orders, MAX(o.orderDate) AS lastOrder, COUNT(ci.cartItemId) AS cartItemCount FROM customers c LEFT JOIN storeCredits sc ON c.customerId=sc.customerId LEFT JOIN orders o ON c.customerId=o.customerId LEFT JOIN cartItems ci ON c.customerId=ci.customerId GROUP BY c.customerId ORDER BY c.lastName ASC

It's definitely adding the last LEFT JOIN that's messing things up, not adding to the SELECT statement. It seems to be multiplying the COUNT items within the query by each other (4 x 2) instead of keeping them isolated and separate.

Any tips?

dalecosp

We'd probably need to see the full table schema ...

It may be you need some more conditionals in there ... you can add AND to the ON:

SELECT c.*, SUM(sc.amount) AS creditTotal, COUNT(o.orderId) AS orders, MAX(o.orderDate) AS lastOrder 
FROM customers c 
LEFT JOIN storeCredits sc ON c.customerId=sc.customerId 
LEFT JOIN orders o ON c.customerId=o.customerId AND o.customerId=sc.customerId 
GROUP BY c.customerId 
ORDER BY c.lastName ASC

bunner_bob

Thanks.

Actually the query you edited is working fine -it's the second query that's messing up. The one where I add the "cartItems" table.

Here a dump of the relevant create tables:

CREATE TABLE cartItems (
cartItemId int(10) unsigned NOT NULL AUTO_INCREMENT,
customerId int(10) unsigned DEFAULT NULL,
sessionId varchar(64) DEFAULT NULL,
contentId varchar(10) DEFAULT NULL,
reservationDate bigint(20) unsigned DEFAULT NULL,
startTime time DEFAULT NULL,
endTime time DEFAULT NULL,
donationName varchar(128) DEFAULT NULL,
anonymous tinyint(4) NOT NULL DEFAULT '0',
inHonorName varchar(128) DEFAULT NULL,
additionalInfo text,
delayedCharge tinyint(3) unsigned NOT NULL DEFAULT '0',
expressPay tinyint(3) unsigned NOT NULL DEFAULT '0',
quantity int(10) unsigned DEFAULT NULL,
price decimal(10,2) DEFAULT NULL,
adjustment decimal(10,2) unsigned DEFAULT NULL,
creditRequested tinyint(4) NOT NULL DEFAULT '0',
creatorId int(10) unsigned DEFAULT NULL,
dateAdded bigint(20) unsigned DEFAULT NULL,
notes text,
PRIMARY KEY (cartItemId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE customers (
customerId int(11) NOT NULL AUTO_INCREMENT,
firstName varchar(48) DEFAULT NULL,
lastName varchar(64) DEFAULT NULL,
alternateName varchar(128) DEFAULT NULL,
company varchar(128) DEFAULT NULL,
address1 varchar(64) DEFAULT NULL,
address2 varchar(64) DEFAULT NULL,
city varchar(64) DEFAULT NULL,
state varchar(4) NOT NULL DEFAULT '',
zip varchar(12) DEFAULT NULL,
phone1 varchar(32) DEFAULT NULL,
phone2 varchar(32) DEFAULT NULL,
email varchar(128) DEFAULT NULL,
passwordHash varchar(40) DEFAULT NULL,
emailList tinyint(4) NOT NULL DEFAULT '0',
notes text,
status varchar(16) DEFAULT NULL,
membershipExpires bigint(20) unsigned DEFAULT NULL,
membershipLevel int(11) DEFAULT NULL,
glassOrientation tinyint(4) NOT NULL DEFAULT '0',
customerProfileId int(10) unsigned DEFAULT NULL,
customerPaymentProfileId int(10) unsigned DEFAULT NULL,
cimCreditCardLastFour varchar(4) DEFAULT NULL,
cimCreditCardExpiration varchar(8) DEFAULT NULL,
creationDate bigint(20) DEFAULT NULL,
modificationDate bigint(20) DEFAULT NULL,
PRIMARY KEY (customerId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE orders (
orderId int(10) unsigned NOT NULL AUTO_INCREMENT,
customerId int(10) unsigned DEFAULT NULL,
shipping decimal(8,2) unsigned DEFAULT NULL,
shippingType varchar(16) DEFAULT NULL,
salesTax decimal(10,2) unsigned DEFAULT NULL,
orderAdjustment decimal(10,2) DEFAULT NULL,
deferredTotal decimal(10,2) unsigned DEFAULT NULL,
expressPay tinyint(4) NOT NULL DEFAULT '0',
total decimal(10,2) unsigned DEFAULT NULL,
orderCost decimal(10,2) unsigned DEFAULT NULL,
amountPaid decimal(10,2) unsigned DEFAULT NULL,
paidViaCreditCard decimal(10,2) unsigned DEFAULT NULL,
paidViaCheck decimal(10,2) unsigned DEFAULT NULL,
paidViaCredit decimal(10,2) unsigned DEFAULT NULL,
paidViaGiftCertificate decimal(10,2) unsigned DEFAULT NULL,
creditCardLastFour smallint(4) unsigned zerofill DEFAULT NULL,
approvalCode mediumint(6) unsigned DEFAULT NULL,
transactionId bigint(20) unsigned DEFAULT NULL,
orderDate bigint(20) unsigned DEFAULT NULL,
orderTime time DEFAULT NULL,
enteredBy int(10) unsigned DEFAULT NULL,
completeDate bigint(20) unsigned DEFAULT NULL,
status varchar(16) DEFAULT NULL,
notes text,
firstName varchar(48) DEFAULT NULL,
lastName varchar(64) DEFAULT NULL,
company varchar(128) DEFAULT NULL,
address1 varchar(64) DEFAULT NULL,
address2 varchar(64) DEFAULT NULL,
city varchar(64) DEFAULT NULL,
state varchar(4) DEFAULT NULL,
zip varchar(12) DEFAULT NULL,
cardTransactionId varchar(32) DEFAULT NULL,
cimCardTransactionId varchar(32) DEFAULT NULL,
errorMessages text,
checkTransactionId int(10) unsigned DEFAULT NULL,
giftCertificateTransactionId int(10) unsigned DEFAULT NULL,
PRIMARY KEY (orderId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE storeCredits (
storeCreditId int(10) unsigned NOT NULL AUTO_INCREMENT,
creditId int(10) unsigned DEFAULT NULL,
customerId int(10) unsigned DEFAULT NULL,
amount decimal(10,2) unsigned DEFAULT NULL,
dateIssued bigint(20) unsigned DEFAULT NULL,
dateRedeemed bigint(20) unsigned DEFAULT NULL,
orderAppliedTo int(10) unsigned DEFAULT NULL,
methodRedeemed varchar(16) DEFAULT NULL,
redeemedBy varchar(16) DEFAULT NULL,
PRIMARY KEY (storeCreditId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Weedpacket

Please use the forum's markup tags when posting to make your posts easier to read.

dalecosp

I'd still look at adding AND clauses to your ON conditions ... the symptoms described are exactly what one would expect from a Cartesian Join...