1
vote
1answer
63 views

Calculating a running balance to store with transactions

I need to put a read-only copy of balance and transaction data from a legacy system, which will only be used to view/print statements. Balance | id | member_id | product_id | balance | | 1 | ...
2
votes
3answers
132 views

Why MAX() of SUM() giving wrong output?

I have a table employee id name salary city 1 ram 50000 c1 2 sham 20000 c2 3 jadu 80000 c1 4 madhu 90000 c4 5 hari 10000 c2 6 gopal 34000 c3 7 ...
2
votes
3answers
56 views

Rolling count of total transactions over time

I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that ...
1
vote
1answer
40 views

Fetch data from same table using two group by clauses in mysql

I have one table name as consultant with 4 fields(id, name,user_type, Created date). I need to write an mysql query for return the count of user_type in each month. In my table, user_type is ...
0
votes
1answer
57 views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
1
vote
2answers
47 views

Query for frequency of e-mail addresses in a field

Newbie here. I know the answer is out there, but lack the vocabulary to ask the right question... I am looking for a MYSQL query which gives me the frequency of addresses in the field emailaddress. ...
0
votes
1answer
153 views

Nested query not recognize external table

i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount. I've made this query but the problem is the inner ...
1
vote
0answers
108 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
1
vote
1answer
616 views

How to get MySQL to iterate a subquery in an aggregate select?

Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does. Consider the following two simple queries: #1 ...
0
votes
0answers
136 views

Select the top N products from each category, but where the merchant hasn't already been picked

this isn't the usual Select top N results from each category question - there's a little bit more to it. I have the Select top N query as follows: SELECT tmp.id, tmp.name, tmp.category_id, ...
0
votes
1answer
217 views

Index for ORDER BY on aggregate in MySQL

I'm working with the following query: SELECT *, SUM(entries_count) AS entries_count FROM leads WHERE promo_id IN (1, 2, 3) GROUP BY user_id ORDER BY entries_count DESC LIMIT 25 OFFSET 100; It's ...
2
votes
1answer
473 views

Is it possible to reduce a group to a row based on some criteria?

I'd like to perform a select on a table involving a GROUP BY such that all rows that share the same set of identifiers are grouped together, but I want to reduce the group to one of the grouped rows ...
1
vote
1answer
130 views

Mysql: Schema/Query Performance Approach for aggregated mailbox folders

I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes: ...
2
votes
1answer
327 views

I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into. I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a ...
1
vote
1answer
190 views

Group activities in activity feed by users and products

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there. One of them is the grouping. I've built it so that if multiple users buys ...
1
vote
1answer
513 views

ORDER BY on aggregate field on a closure table giving unexpected results

I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables. The idea of faceted navigation might look like this: It's ...
2
votes
2answers
2k views

How do I create a user-defined aggregate function?

I need an aggregate function that MySQL doesn't provide. I would like it to be in MySQL's flavor of SQL (that is, not in C). How do I do this? What I'm stuck on is creating an aggregate function -- ...
1
vote
4answers
3k views

Should I use the Infobright database or is there a better aggregation solution?

We are a MySQL front-end shop and have a system currently logging event data in a highly structured format. Think apache traffic log. We need to be able to aggregate counts of this data (cube) vis a ...
2
votes
1answer
553 views

Calculate percentage of false records to total records within a aggregate function

I'm looking for a solution to calculate a percentage rate of a boolean value: Model: Products 1 <-> n Components Components.essential is a boolean attribute What I want to calculate ...
4
votes
2answers
232 views

MySQL aggregate anomaly

I was checking selectivity of some columns for an index. Where is this "ignore what I give you" behaviour documented? This gives 4,851,908, 4,841,060, and 1,000,052 SELECT COUNT(*), ...
5
votes
2answers
540 views

Is it a good idea to split MySQL DB into two servers

I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate: I ...
44
votes
4answers
5k views

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema ...