CODEWITHSUNDEEP
mysqlsqldatabase
lateralus
lateralus

Reputation: 1030

SQL query to make difficult data aggregation

I need to extract data from a MySQL database to make a report, and I'm stuck in something I've never seen before. Suppose I have data on 4 hardware components, and a customer can make requests for ram, ip, cpu and backup components. Every record of my table has a "quantity" field and a "component" field, so if a customer wishes to buy 4 ram quantity will get the value 4 and component the value ram.

Each record can be an activation of the product subscription (with component and quantity equals to NULL) or the request for components (one record per type of component, so if a customer wants to buy 4 ram and 1 ip I will have an activation, the request with 4 ram and a request with 1 ip ).

In the aggregation report I need a column for each component, so a cpu column, an ip column etc. I need to populate the values with NULL if the customer has just registered or did not order components of that kind, or with the quantity if he bought one of these components.

Let's make it practical:

What I want is something like:

How can I accomplish something like that? I somehow need to make the possible values of component column as new different columns, and fill them with the values of quantity column.

Upvotes: 0

Views: 77

Answers (2)

MiiinimalLogic
MiiinimalLogic

Reputation: 818

I hate to say it but this database design can create confusion because you're grouping 'objects' that you shouldn't. For example, you should have a table of just registrations and one table of orders with parentIds of the orderers..but I digress. If I understand your question correctly:

To solve this problem, you'll need sub or multiple queries for example one to select * where ip is Null AND cpu is Null AND ram is NULL, then sum the result and insert into quantity column where id = this id

one to select where cpu is Null, one to select where others are null then sum the result and insert into quantity column where id = this id,

… and so on.

Then parse the output from each query.

Upvotes: 0

eemikula
eemikula

Reputation: 93

Why not use cases on the possible components?

select
    CASE 
        WHEN component='ip' 
        THEN quantity
        ELSE NULL 
    END as 'ip',
    CASE 
        WHEN component='ram' 
        THEN quantity
        ELSE NULL 
    END as 'ram',
    CASE 
        WHEN component='cpu' 
        THEN quantity
        ELSE NULL 
    END as 'cpu',
    CASE 
        WHEN component='backup' 
        THEN quantity
        ELSE NULL 
    END as 'backup'
from table;

Upvotes: 1

Related Questions