Hi, I'm Bhavik. Welcome to JustAnswer. I'm reviewing your question now, and will reply back ASAP.
I think I got your question, I do not have any doubt as of now in requirement.
Please share your existing code and sample database or table structure if any so that I can pick it up from there.
Yes, I got it.
I was about to ask you the same question.? Is this is urgent ? If so then I can give more time to it.
Also it would be great if you could give me the expected results for some of the data so that I can see if I am getting correct results or not.
Yes, I can see column G is the running total. What about column H ?
Yes, I can see the formula's now, It is giving reference error in some initial rows of column G.
I will give you query to achieve this in sql as soon as possible.
Yes, I got both the sheets.
I try to give you working code within 24 hrs from now.
Yes, I have couple of things for today to look for. But I try to complete your work as soon as I can.
Yes, I think, I will be able to do it. I have done this before ( around 2 to 3 years ago ). I am in India so it is currently 23:40 here so it's almost end of my day today.
That's Great !!
I am sorry, I could not do that. I am opting out so that another expert can look into it.
I used this to get running total but could not do anything further that ,Not sure how to take care of values which we are getting at row 13 onwards
Below will give you running total.
SELECT a.id, a.name,a.amount, (SELECT SUM(b.amount) FROM NAMES b WHERE b.id <= a.id GROUP BY b.name)FROM NAMES aORDER BY a.id,a.name;
Soon someone will pick it up.
Hello. I built the world's first multi-level secure RDBMS, Trusted RUBIX, including its SQL parser and ESQL/C precompiler.
I also wrote the proposed Multi-level Security Extensions to ANSI SQL Level II.
Perhaps I can assist you with your problems?
Your first problem right off the bat is that you can't use GROUP BY or HAVING in a subquery, only at the top level. If some product supported it--which one doubts severely--it is entirely non-standard and unreliable. ALWAYS stick to the standard; do not become dependent upon vendor-specific niceties.
Also, in your sample query, "Row Thirteen" doesn't help me. I can't see your data, and I don't see that the query per se incorporates any row number semantics.
Not sure why you would need such a tortured formulation, anyway, for what seems very straightforward. Have you posted the SCHEMA (that is, the CREATE TABLE statements) so that I can see what tables have what columns and what keys?
I will look at your spreadsheet and see what I can figure out. Stand by. Thanks.
You are computing F, G, and H based upon A through E, I take it?
Interesting how EVERY row features the value 'DART' in column A. This renders it moot for inclusion in the table. Simply zap that column--UNLESS this is part of a BIGGER spreadsheet that includes values OTHER THAN 'DART'. Otherwise, we are needlessly toting around excess information.
Humor me and tell me how those three columns are calculated. I need a description like the following:
For every set of rows with the same value of FOO, count how many unique values of BAR occur.
For every set of rows with the same value of FOO, determine the average value of AWK within each set of rows that have the same ZIZ; then determine the average of all those averages.
Guess what: in SQL, there is no such thing as "a simple sequencing." SQL does not support the notion of sequences in that manner; tables are, by definition, unordered unless you IMPOSE an order via "ORDER BY."
Wait--you'll say "what about serial keys!" That's different: those are automatically assigned upon INSERT. Different beast altogether.
LOL I am happy to humor you, however we must meet halfway. Adding a "simple sequencing" won't serve any purpose but to require tortured mechanisms and to obfuscate anything else that depends upon it. There is guaranteed to be a simpler way. Never mind the "sequencing" and the "rolling": can you please provide the type of description for which I asked above?
"For every set of rows with the same value of FOO, . . ."
Formulas--presumably copy/pasted from here and there--capture row-number-based specifics that have nothing to do with the STRUCTURE of the data. I require a formulation that makes semantic sense. SQL can calculate, say, the average COST for every DEPT that doesn't have three or more unique values of BLOOB; it CANNOT calculate the average cost for "rows 2411-2569," as there's no such thing in its world.
Let's try one more time: what specific problem are you trying to solve by including columns F, G, and H? What summary statistics, for example, do you want to derive?
I see, right away, that one column is calculated based upon whether this value is greater than the value in the predecessor row.
SQL has no notion of predecessors--NONE AT ALL.
An SQL table is not a spreadsheet. The concepts don't map one-to-one.
SQL requires a different sort of reasoning: "set-at-a-time." But that doesn't mean you derive some bizarre column, F, that makes no sense WITHIN THE SQL WORLD, and then derive G based upon that, and then derive H based upon that.
What PROBLEM are you trying to solve with these three added columns?
That's what I'm saying. You have to solve the problem appropriately.
You talk of twelve-month rolling totals. Funny, I don't see any months or dates anywhere. How is SQL supposed to determine what transaction relates to what month?
If "that's all," then kindly provide actual dates in some column somewhere.
What exactly are 12 and 24? Let me take a wild guess: things just happen to "roll over" every twelve months--even though there is no association with calendar years.
If you want things to be based upon dates, SQL need dates, not some seat-of-the-pants representation.
Also, "% Change in Column H is not a "that's all," either. % change of what with relation to what?
No, actually, I'm just trying to obtain a clear description of the problem.
Any SQL expert will proceed in precisely the same manner. SQL needs actual dates. It does not know from "neighboring" rows. It does not understand things that roll per twelves or twenty-fours. If you provide ACTUAL DATES, the problem can be solved VERY READILY.
Now we are getting somewhere. I suspected those were Julian. They can readily be converted to human-readable.
Now, please re-describe F, G, and H in terms of dates and years, not in terms of "the previous row" (no such thing in SQL) or "whenever column D changes" (again, no such thing in SQL, because changes means 'changes from previous row,' which there's no such thing in SQL).
Did that throw you for a loop? "There's no such thing as the previous row in SQL"? It happens to be true. Orders are only imposed by human readers at the last minute. Relations operate strictly upon independent tuples (rows of data, or pieces of rows). No such animal as "previous."
Column F will vanish, I should think. It seems to be a stopgap that somehow enables you to calculate G and H. When G and H are properly calculated, there will be no need for F.
I may be missing something, as I don't see any months at all in D, just text strings.
By ROLLING, you refer to the twelve months IMMEDIATELY PRECEDING THIS ROW?
In the next row, the ROLLING total (column G) will have dropped off the oldest record from the last calculation, and picked up a fresh one?
In column H, you want the year-to-year variation. You want this recalculated in rolling fashion, every twelve months? You wait for 24 because there's no such thing as "one year compared to the previous year" until you've collected two years?
O.K., and (assuming arguendo that there are twelve monthly records per year JUST FOR ILLUSTRATIVE PURPOSES) the rolling total reflects the last twelve records, and the % change reflects this record and the twelve-records-ago record?
That is readily done.
Interesting how your spreadsheet wasn't usefully sorted by ALLOCMON.
By the way, ALLOCMON isn't the best name for a column that contains a month/day/year--unless your only interest is the month and the year. Please advise.
And now we are totally lost by "everything starts when there is a change in column D."
PLEASE don't consider "changes" (I thought I said several times that there is no such thing as a sequence or a predecessor). Please consider GROUPINGS.
You want a DIFFERENT rolling total PER month, PER subcategory? I see that I had to torture this requirement out of you!
Well, it doesn't appear that way to me. It appears to be sorted by Client, Allcmonth, Category, Subcat.
HUMBLE SUGGESTION: stick to your spreadsheet, since you have a crystal-clear idea of what you want, and each question I ask about what you claim to want causes the Hydra to sprout two more heads.
Since we are obviously unable to fulfill your goal--including the SQL internals guru who knows every last subtle semantic of the language and, what's more, has implemented the ENGINE to effectuate it--I will provide you a refund.
Your spreadsheet works. It supports the notions with which you are accustomed to working, such as "predecessor" and "sortings" that change from the beginning of the sentence to the end of the sentence. I WILL GET YOU A FULL REFUND.