Urgent: Need help summing values and showing the total by date

[OedipaMaas]

I know this is probably pretty simple....for some of you...but I think I've just been at this far too long.  I'm also bad at calculation logic.

 

Ok the version is: MySQL client version: 5.1.37

 

The magic all happens in one form transaction table that more or less looks like this:

 

DATE:  datetime (on insert)

ID: autoincremented id on insert

Purchase Option1: string (can be empty)

Purchase Option2: string (can be empty)

Purchase Option3: string (can be empty)

Purchase Price1: 60

Purchase Price2: 60

Purchase Price3: 125 or 40 (depending on what form they entered on...which is how I want to display cust_type in the query)

Email: string

 

In short, here's what I need:

 

A sum total of all purchases by distinct date:

 

Something like this....

 

2010-2-10 - $1225

 

(which might reflect PurchaseOption1 + Purchase Price3 as was selected by one person and then all three of the purchase options were selected by three other people on the same day...all the transactions have their own rows....and I want the total to reflect the sum of ALL those purchases for that day)

 

Like I said, the table is transactional.  I know, not the best thing.  It's been a headache.  I just need the right nudge for getting this part of the query done.  I've been messing around with COUNT and SUM and GROUP BY and my head's about to explode.

 

THANKS in advance!!!!!

 

[Zane]

Would have helped a lot more if you had shown us what you already tried..

 

But here's a code-worthy suggestion.

SELECT sum(purchase1, purchase2) as DailySum from TheTable GROUP BY date

[OedipaMaas]

Sorry I didn't include the previous queries I've tried.  they ended up becoming so garbled and I'm just about brain dead...

 

I tried this:

 

SELECT sum(proregAB_PRICE, proregCD_PRICE, proregCONF_PRICE) as DailySum,

DATE(sub_date) AS PurchDATE

FROM table

GROUP BY sub_date

 

But (and this is the stupid flaw in the design of the transactional table), the prices are stored in the table simply as referential values.  But I don't want to sum them unless I know that the PurchaseOption1, 2 or 3 field is empty or not.

 

So I have to test those three fields, I guess, and sum them if they are populated with a string or not.  Ugh.  I don't even know how to approach that part to be honest.  I've tried endless variants of what you posted here for the last few hours...

 

 

[gizmola]

Take a look at mysql IF() operator.

 

Also please don't describe your posts as Urgent.  It *almost* made me skip your thread.  We spell out our distaste for that in the rules.

[OedipaMaas]

I'm sorry.  I'm obviously new here.  I did read the topic rules, but like I said, I've been coding stuff for HOURS and I'm hitting the wall....thanks for reading my post regardless.

[gizmola]

Well, hopefully you get the idea, that you can use the IF() for each column to pass back a zero even if it's null.  IF() basically is a mysql version of the php ternary operator.

 

[OedipaMaas]

Ok, that sortof helps.

 

SELECT COUNT(*)) AS registrants, SUM(total) AS ordertotal, sub_date AS PurchDate FROM data IF(preregAB <> '' OR preregCD <> '')

GROUP BY PurchDate

 

Would that return a 0?  I'll start research how to use this statement now. 

[OedipaMaas]

That IF() statement really helps...

 

I'm about to fall asleep at my keyboard, but it's good that I feel like I crawled forward another inch on this whole damn thing:

 

SELECT IFNULL( preregAB,  'yes' ) , IFNULL( preregCD,  'yes' ) , IFNULL( CONF,  'yes' )

FROM eventreg

 

Now I just gotta figure out how to return a count, using this IF statement, of how many of each one of the conditions above exist.  Then I'll be halfway there.  After that, I still gotta take those counts and SUM them by date.  Sigh.

[gizmola]

No, what you want to do is add the 3 columns together, if I understand your issue.

 

I'll phrase what I think you meant this way:

 

"I have a table, that has 3 columns in it.  Any of the 3 columns could have a value.  I need to sum a bunch of rows, using whatever number(s) are available for any of the 3 columns."

 

So the IF() just helps that you can add them all together, regardless if they have a value or not using something like SUM((IF(onecolumn > 0, onecolumn, 0) + IF(twocolumn > 0, twocolumn, 0) + ...)

 

At this juncture we really need to see a describe or export for the table in question.  Your names keep changing in your descriptions.  If it's truly a datetime column, in order to group by date you need to either apply a > and

[OedipaMaas]

Yeah, I'm sorry I didn't include the table sql earlier.  Had it in my head it would be a simple answer I just wasn't seeing...

 

Ok, so I've attached the table.  Your assessment is true.  And it sounds so simple, right?  It's really worn me out trying to figure it out...

 

[attachment deleted by admin]

[gizmola]

It could be simple, if in fact it's ok to just add these columns: 

 

  `proregAB_PRICE` int(6) DEFAULT NULL,

  `proregCD_PRICE` int(6) DEFAULT NULL,

  `proregCONF_PRICE` int(6) DEFAULT NULL,

 

 

I'm guessing this is some sort of conference application, so yeah it looks like that would be safe to do.  I don't see a datetime column in the whole table, so not sure where you're getting the date from, unless it requires a join, or this is not the actual table structure.

 

That is truly one ugly non relational table --- you have my condolences. 

 

 

[OedipaMaas]

Hi,

 

Yeah, it is freakin' ugly.  Sigh.  Anyway, yes the DATETIME field is in there...as sub_date.

 

So really, changing the columns, huh?  Ugh.  It's an active database right now....  Dreadful....

 

Guess I'll sleep on it.

[gizmola]

Ok, so just to be clear whenever you read this... I'm not suggesting you change anything about the database.  It sucks, and if you could change it that would be great, but I make no assumptions about that.

 

What I meant was you can use the SUM(IF() + IF() + IF()) I described on the 3 columns I listed, if that works ok.  My point is, that it is easy, and doesn't require you to cross reference the other varchar columns -- if there's a number in there it will return it and ultimately sum it.  The only problem would be that if the application is so poorly written that data gets put into those columns in certain situations where it *shouldn't be added* then you'd need something more complicated, which seems like what you were hinting at earlier.  IF so ---

 

If that's true you should be able to do something like  IF(IF(LENGTH(preregAB_PRO > 0, true, false), IF(proregAB_PRICE > 0, proregAB_PRICE, 0), 0) + ....

 

But basically this is just a variation on what I originally wrote.

 

FWIW, the attachment you provided has no sub_date column.  However, the important thing again, is that IF sub_date is a datetime you'll need to GROUP BY Date(sub_date).  Otherwise you'll not get a grouping on day, and basically you'll have a total for every order, which isn't what it seems you want, since it seems you want a total by day.

 

 

[OedipaMaas]

Thanks for your reply!  I'm going to give this a shot now....and I'll let you know.