Because it’s useful and that last part isn’t true? Databases do more than indexes and you have trade offs regarding the impact of adding too many indexes.
It's also not only not a problem for scalability but in reality an important way to _improve_ scalability. Consider a simple example where we do "SELECT SUM(price) FROM orders GROUP BY customer_id". Removing SUM() from the language would massively increase the amount of data which needs to be processed into the response — which makes anything else you're doing like sorting harder – and it prevents various optimizations the database engine might make. For example, on AWS RDS Aurora the database pushes all of that down to the storage nodes so each storage node will return the sums for the records which are on that node and the main node can sum those intermediate values without needing to transfer a single source row over the network. Since SUM() is part of the language and well-defined, that's safe for one database team to implement without the risk of their results not matching a competing database.
Other things to think about are computed views and stored procedures: in both cases, there are situations where these are dramatic performance improvements or otherwise desirable and having a richer language means that those constructs can solve more problems.
It sounds like you don’t want a general purpose database. Something like what you describe might be more efficient on certain queries but only because it’s giving up a lot of flexibility - consider what happens if you add any filtering or grouping changes and those partial sums can no longer be used.
This preference for general systems rather than optimizing for a single query shows through in the tools used for this kind of task: materialized views and indexes using expressions. Those would allow the kind of precomputed aggregates you’re talking about without the kinds of challenges around flexibility and correctness your approach would encounter.
> Transferring large amounts of data in the response is solvable by using shared memory
That’s a common optimization but it’s not a panacea any more than indexes are. For example, relying on that falls over badly once you need a network and the problems I mentioned before are still important — even if your shared memory implementation is perfectly efficient, you’re still looking at keeping data in memory longer so your client can aggregate it when the database engine could do that for you and discard the memory immediately.
Another way of looking at this is to ask why a field with billions in R&D hasn’t already done it, and especially why various big data tools have been significantly less impactful than promised. If you see what seems like an easy way to beat the current options, it’s usually a good time to ask why everyone working in that space hasn’t seen it.
If you're doing a query that is not backed by an index, it's not scalable, so you could also do it in Excel.
> This preference for general systems rather than optimizing for a single query shows through in the tools used for this kind of task: materialized views and indexes using expressions.
Materialized views totally suck (at least in postgresql), because you cannot update them incrementally (yet). A better option most of the time are triggers and aggregate columns, but it requires a lot of micromanagement.
> It sounds like you don’t want a general purpose database
I don't think I want a special feature, map-reduce indexes seem pretty general "mathematically" sound thing.
> Another way of looking at this is to ask why a field with billions in R&D hasn’t already done it, and especially why various big data tools have been significantly less impactful than promised.
Well that's what's exiting about research, that some things haven't been done yet. Even though they may seem fairly obvious.