All geek questions in one place

SQL query - counting new defined values ​​from CASE

I have a problem with SQL Query. I tried many hours and searched the Internet, but I can not find what I need.

I have 2 tables that are linked through an identifier as a primary key and a foreign key. The values ​​inside the second table have a specific category. Each value in the second table refers only to a separate category. I need to sort this inside a query in new categories. Now multiple values ​​can have the same category. The desired result is a list of these new categories with their respective counter, including 0.

I tried different associations with subtitles, etc., but I did not get the desired result.

Unfortunately, I cannot show you the excact / query tables, so I tried to replicate the problem (I hope this is clear)

My tables:

meals:
| id | name | food_id |
+----+------+---------+ 
| 01 | Pete | 001     | 
| 02 | Anna | 002     |
| 03 | Jim  | 003     |
| 04 | John | 002     |
| 05 | Lucy | 003     |

food:
| id  | name   |
+-----+--------+    
| 001 | Apple  |    
| 002 | Banana | 
| 003 | Carrot | 
| 004 | Steak  |

My request:

SELECT      food.foodtype           AS foodtype,
            COUNT(food.foodtype)    AS amount

FROM        (
            SELECT  food.id         AS id
                    CASE WHEN food.name = 'Apple'   THEN 'Fruit',
                         WHEN food.name = 'Banana'  THEN 'Fruit',
                         WHEN food.name = 'Carrot'  THEN 'Vegetable',
                         WHEN food.name = 'Steak'   THEN 'Meat',
                    END             AS foodtype
            FROM    food
            )                       AS food

INNER JOIN  meals
ON          meals.food_id = food.id

GROUP BY    food.foodtype

The result of this query is a table with all types of food used, but I also need unused food types displayed as 0.

| foodtype  | amount |
+-----------+--------+
| Fruit     | 3      |
| Vegetable | 2      |

As a result, I need:

| foodtype  | amount |
+-----------+--------+
| Fruit     | 3      |
| Vegetable | 2      |
| Steak     | 0      |

Another idea that I had, but still not needed:

SELECT      CASE WHEN food.name = 'Apple'   THEN 'Fruit',
                 WHEN food.name = 'Banana'  THEN 'Fruit',
                 WHEN food.name = 'Carrot'  THEN 'Vegetable',
                 WHEN food.name = 'Steak'   THEN 'Meat',
            END                     AS foodtype
            COUNT(meals.id)         AS amount

FROM        meals

RIGHT JOIN  food
ON          food.id = meals.food_id

GROUP BY    food.name

My result:

| foodtype  | amount |
+-----------+--------+
| Fruit     | 1      |
| Fruit     | 2      |
| Vegetable | 2      |
| Meat      | 0      |

I hope this is understandable, what I need, and someone can help me with this problem.

+4
source share
7 answers

Check this.

            SELECT foodtype,COUNT(m.id) as amount 
            FROM        
            (
                        SELECT  id AS id,
                                name,
                                CASE WHEN name = 'Apple'   THEN 'Fruit'
                                     WHEN name = 'Banana'  THEN 'Fruit'
                                     WHEN name = 'Carrot'  THEN 'Vegetable'
                                     WHEN name = 'Steak'   THEN 'Meat'
                                END             AS foodtype

                        FROM    food
                        ) AS food

            left JOIN  meals m
            ON          m.food_id = food.id

            GROUP BY    foodtype
            order by foodtype

Output

enter image description here

+1
source

LEFT JOIN , . INNER JOIN , , , .

SELECT food.foodtype AS foodtype,
       COUNT(meals.food_id) AS amount
FROM
(
    SELECT  food.id AS id,
            CASE WHEN food.name = 'Apple'   THEN 'Fruit'
                 WHEN food.name = 'Banana'  THEN 'Fruit'
                 WHEN food.name = 'Carrot'  THEN 'Vegetable'
                 WHEN food.name = 'Steak'   THEN 'Meat'
            END AS foodtype
    FROM food
) AS food
LEFT JOIN meals
    ON meals.food_id = food.id
GROUP BY food.foodtype
+2

IMHO , .

, :

FOODTYPE (table)
ID    NAME
001   Fruit
002   Vegetables
003   Meat

FOOD (table)
ID    NAME    FOODTYPE_ID
001   Apple   001
002   Banana  001
003   Carrot  002
004   Steak   003

MEAL (Table)
ID   NAME   FOOD_ID
01   Pete   001
02   Anna   002
03   Jim    003
04   John   002
05   Lucy   003

:

SELECT ft.name,
    (SELECT count(*)
    FROM food f
    JOIN meal m
        ON f.id  m.food_id
    WHERE f.foodtype_id = ft.id)
FROM foodtype ft

...

:

select 
    x.foodtype, 
    (select COUNT(*)
    from meal m
    join food f
        on m.food_id = f.id
    where case
         when f.name = 'Apple' or f.name = 'Banana' then 'Fruit'
         when f.name = 'Carrot' then 'Vegetables'
         when f.name = 'Steak' then 'Meat'
    end = x.foodtype
    )
 from
     (select distinct
     case
         when f.name = 'Apple' or f.name = 'Banana' then 'Fruit'
         when f.name = 'Carrot' then 'Vegetables'
         when f.name = 'Steak' then 'Meat'
    end foodtype
    from #food f) as x
0
CREATE TABLE #meals
    ([id] int, [name] varchar(4), [food_id] int)
;

INSERT INTO #meals
    ([id], [name], [food_id])
VALUES
    (01, 'Pete', 001),
    (02, 'Anna', 002),
    (03, 'Jim', 003),
    (04, 'John', 002),
    (05, 'Lucy', 003)

CREATE TABLE #food
    ([id] int, [name] varchar(6))
;

INSERT INTO #food
    ([id], [name])
VALUES
    (001, 'Apple'),
    (002, 'Banana'),
    (003, 'Carrot'),
    (004, 'Steak')
;


SELECT food.foodtype AS foodtype,
       COUNT(meals.food_id) AS amount
FROM
(
    SELECT  id AS id,
            CASE WHEN name = 'Apple'   THEN 'Fruit'
                 WHEN name = 'Banana'  THEN 'Fruit'
                 WHEN name = 'Carrot'  THEN 'Vegetable'
                 WHEN name = 'Steak'   THEN 'Meat'
            END AS foodtype
    FROM #food
) AS food
FUll JOIN #meals meals
    ON meals.food_id = food.id
GROUP BY food.foodtype
order by food.foodtype


foodtype    amount
Fruit        3
Meat         0
Vegetable    2
0

case left join:

DECLARE @meals TABLE ( id INT, fid INT )
DECLARE @food TABLE ( id INT, name VARCHAR(MAX) )

INSERT  INTO @meals
        ( id, fid )
VALUES  ( 1, 1 ),
        ( 2, 2 ),
        ( 3, 3 ),
        ( 4, 2 ),
        ( 5, 3 )

INSERT  INTO @food
        ( id, name )
VALUES  ( 1, 'apple' ),
        ( 2, 'banana' ),
        ( 3, 'carrot' ),
        ( 4, 'steak' )

SELECT  CASE WHEN f.name = 'Apple' THEN 'Fruit'
             WHEN f.name = 'Banana' THEN 'Fruit'
             WHEN f.name = 'Carrot' THEN 'Vegetable'
             WHEN f.name = 'Steak' THEN 'Meat'
        END AS ft ,
        COUNT(m.ID) AS amount
FROM    @food f
        LEFT JOIN @meals m ON f.id = m.fid
GROUP BY CASE WHEN f.name = 'Apple' THEN 'Fruit'
              WHEN f.name = 'Banana' THEN 'Fruit'
              WHEN f.name = 'Carrot' THEN 'Vegetable'
              WHEN f.name = 'Steak' THEN 'Meat'
         END
ORDER BY amount desc

:

ft          amount
Fruit       3
Vegetable   2
Meat        0

:

;WITH    cte
          AS ( SELECT   CASE WHEN f.name = 'Apple' THEN 'Fruit'
                             WHEN f.name = 'Banana' THEN 'Fruit'
                             WHEN f.name = 'Carrot' THEN 'Vegetable'
                             WHEN f.name = 'Steak' THEN 'Meat'
                        END AS ft ,
                        m.id
               FROM     @food f
                        LEFT JOIN @meals m ON f.id = m.fid
             )
    SELECT  ft ,
            COUNT(ID) AS amount
    FROM    cte
    GROUP BY ft
    ORDER BY amount DESC
0

, , , , , " ". :

SELECT
x.foodtype,
SUM(X.amount) AS amount
FROM (
SELECT      CASE WHEN food.name = 'Apple'   THEN 'Fruit',
         WHEN food.name = 'Banana'  THEN 'Fruit',
         WHEN food.name = 'Carrot'  THEN 'Vegetable',
         WHEN food.name = 'Steak'   THEN 'Meat',
        END                     AS foodtype
        COUNT(meals.id)         AS amount

FROM        meals

RIGHT JOIN  food
ON          food.id = meals.food_id

GROUP BY    food.name
) AS x
GROUP BY x.foodtype
;

: Stackoverflow, , , ;)

: "" , "", .

0

;with
cat as (
    select *
    from (
    values
    ('Apple' , 'Fruit'     ),  
    ('Banana', 'Fruit'     ),
    ('Carrot', 'Vegetable' ),
    ('Steak' , 'Meat'      )
    ) x (food_name, foodtype)
)
select foodtype, count(m.id) amount
from cat c 
left join food f on f.name = c.food_name
left join meals m on m.food_id = f.id
group by foodtype


foodtype    amount
Fruit       3
Meat        0
Vegetable   2
0

Source: https://habr.com/ru/post/1663932/

More articles:


All Articles