SQL - Columns for Different Categories

Different columns for different categories in SQL

Create a single table with all those columns along with a new column named MobileType. You might insert 1 for "Touch Mobile" and 0 for "Other Mobile".
If you are inserting information of a "touch mobile" then input 1 in "MobileType" field and all the touch mobile related informations in relative field. Leave "other mobile" related fields as NULL. Do the opposite for "other mobile" type entry.

Make MobileType,Price,Modile not null field and others Null field and add below constraint which will force your condition:

ALTER TABLE mobile ADD CONSTRAINT CK_mobile CHECK(
    Refreshrate is null and Resolution is not null and  Latency  is not null and Isblackandwhite is null and Screensize is null
    OR
    Refreshrate is  null and Resolution is  null and  Latency  is  null and Isblackandwhite is not null and Screensize is not null
    )

SQL - columns for different categories

Use:

  SELECT t.student,
         MAX(CASE WHEN t.test = 'T1' THEN t.grade END) AS T1,
         MAX(CASE WHEN t.test = 'T2' THEN t.grade END) AS T2,
         MAX(CASE WHEN t.test = 'T3' THEN t.grade END) AS T3
    FROM TABLE t
GROUP BY t.student

Creating columns for each category in column

Use a pivot query:

SELECT
    Company,
    MAX(count) FILTER (WHERE type = 'sync')  AS sync,
    MAX(count) FILTER (WHERE type = 'async') AS async
FROM yourTable
GROUP BY
    Company
ORDER BY
    Company;

Demo

Category column in SQL

I think if you change a little bit, for example, category column in categories ID,

`---------------------------------
| ID   | ITEM      | CATEGORY   |
---------------------------------
| 1    | COOKIES   | 1          |
| 2    | CAKE      | 1          |
| 3    | WATER     | 2          |
| 4    | PEANUTS   | 1          |
| 5    | PEPSI     | 2          |

Plus a categories table

--------------------
| ID   | ITEM      | 
--------------------
| 1    | FOOD  
| 2    | DRINK     

AND query:

SELECT * FROM `table` WHERE category=1;   (or 2)

Mysql query with subquerys for each column with different categories

You can use conditional aggregation, if you know exactly what categories you have:

select max(case when categoryid = 1 then theme end) as cat1,
       max(case when categoryid = 2 then theme end) as cat2,
       . . .
from (select th.*,
             row_number() over (partition by th.idcategory order by th.idtheme) as seqnum
      from themes th
     ) th
group by seqnum;

Repeat the logic for each column you want in the result set.

Using group by on multiple columns

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

Table: Subject_Selection

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

You will get something like:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

...because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

we would get this:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

---

Related Topics