OedipaMaas Posted February 12, 2010 Share Posted February 12, 2010 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!!!!! Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/ Share on other sites More sharing options...
Zane Posted February 12, 2010 Share Posted February 12, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011236 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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... Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011240 Share on other sites More sharing options...
gizmola Posted February 12, 2010 Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011241 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011244 Share on other sites More sharing options...
gizmola Posted February 12, 2010 Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011246 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011248 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011259 Share on other sites More sharing options...
gizmola Posted February 12, 2010 Share Posted February 12, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011262 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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] Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011267 Share on other sites More sharing options...
gizmola Posted February 12, 2010 Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011277 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011279 Share on other sites More sharing options...
gizmola Posted February 12, 2010 Share Posted February 12, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011287 Share on other sites More sharing options...
OedipaMaas Posted February 12, 2010 Author Share Posted February 12, 2010 Thanks for your reply! I'm going to give this a shot now....and I'll let you know. Quote Link to comment https://forums.phpfreaks.com/topic/191859-urgent-need-help-summing-values-and-showing-the-total-by-date/#findComment-1011403 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.