SQL - count amount of occurences for items in different price diapasons

Question

I have a question about SQL, and I honestly tried to search methods before asking. I will give an abstract (but precise) description below, and will greatly appreciate your example of solution (SQL query).

What I have:

1. Table A with category ids of the items and prices (in USD) for each item. category id has int type of value, price is string and looks like "USD 200000000" (real value is multiplied by 10^7). Tables also has a kind column with int type of value.

2. Table B with relation of category id and name.

What I need:

Get a table with price diapasons (like 0-100 | 100-200 | ...) as column names and count amount of items for each category id (as lines names) in all of the price diapasons. All results must be filtered by kind parameter (from table A) with value 3.

Questions, that I encountered (and which caused to ask for an example of SQL query):

1. Cut "USD from price string value, divide it by 10^7 and convert to float.
2. Gather diapasons of price values (0-100 | 100-200 | ...), with given step in the given interval (max price is considered as unknown at the start). Example: step 100 on 0-500 interval, and step 200 for values >500.
3. Put diapasons of price values into column names of the result table.
4. For each diapason, count amount of items in each category (category_id). Left limit of diapason shall not be considered (e.g. on 1000-1200 diapason, items with price 1000 shall not be considered).
5. Using B table, display name instead of category id.

Response is appreciated, ignorance will be understood.

Answer 1

If you only need category ids, then you do not need B. What you are looking for is conditional aggregation, something like:

select category_id,
       sum(case when cast(substring(price, 4, 100) as int)/10000000 < 100 then 1 else 0 end) as price_000_100
       sum(case when cast(substring(price, 4, 100) as int)/10000000 >= 100 and cast(substring(price, 4, 100) as int)/10000000 < 200
                then 1 else 0
           end) as price_100_200,
       . . .                 
from a
group by category_id

Answer 2

There is no standard way to do what you describe.

That is because to do (3) you need a pivot aka crosstab, and this is not in ANSI SQL. Each DBMS has it's own implementation. Plus dynamic columns in a pivot table are an additional complication.

For example, Postgres calls it a "crosstab" and requires the tablefunc module to be installed. See this SO question and the documentation. Compare to SQL Server, which uses the PIVOT command.

You can get close using reasonably standard SQL.

Here is an example based on SQLite. A little bit of conversion would provide a solution for other systems, e.g. SUBSTR would be substring(string [from int] [for int]) in postgre.

Assuming a data table of format:

and a category name table of:

then the following code will produce:

WITH dataCTE AS
    (SELECT product_id AS 'ID', CAST(SUBSTR(price, 5) AS INT)/1000000 AS 'USD',
    CASE WHEN (CAST(SUBSTR(price, 5) AS INT)/1000000) <= 500 THEN
        100 ELSE 200
        END AS 'Interval'
    FROM data
    WHERE kind = 3),
groupCTE AS
    (SELECT dataCTE.ID AS 'ID', dataCTE.USD AS 'USD', dataCTE.Interval AS 'Interval',
    CASE WHEN dataCTE.Interval = 100 THEN 
        CAST(dataCTE.USD AS INT)/100
    ELSE
        (CAST(dataCTE.USD-500 AS INT)/200)+5
    END AS 'GroupID'
    FROM dataCTE),
cleanCTE AS
    (SELECT *, CASE WHEN groupCTE.Interval = 100 THEN
        CAST(groupCTE.GroupID *100 AS VARCHAR) 
        || '-' || 
        CAST((groupCTE.GroupID *100)+99 AS VARCHAR)
    ELSE
        CAST(((groupCTE.GroupID-5)*200)+500 AS VARCHAR) 
        || '-' || 
        CAST(((groupCTE.GroupID-5)*200)+500+199 AS VARCHAR) 
    END AS 'diapason'
    FROM groupCTE
    INNER JOIN cat_name AS cn ON groupCTE.ID = cn.cat_id)
SELECT *
FROM cleanCTE;

If you modify the last SELECT to:

SELECT name, diapason, COUNT(diapason)
FROM cleanCTE
GROUP BY name, diapason;

then you get a grouped output:

This is as close as you will get without specifying the exact system; even then you will have a problem with dynamically creating the column names.