Sql statement query count data

Question

I start to learn how to write sql language but I got stuck with the problem below : Now I have a data in a table named 'data'

+------+-------+-------+-------+-------+
| id   | name  | type1 | type2 | type3 |
+------+-------+-------+-------+-------+
|    1 | Cake  | a     | b     | f     |
|    2 | Coca  | a     | d     | c     |
|    3 | Ice   | c     | b     | a     |
|    4 | Wine  | c     | e     | d     |
|    5 | Salad | c     | f     | a     |
|    6 | Water | d     | e     | f     |
+------+-------+-------+-------+-------+

I want to write an sql statement to count all type that present in type1, type2, type3 so the result I want to get is

+------+------+
| type | count|
+------+------+
|    a | 4    |
|    b | 2    |
|    c | 4    |
|    d | 3    |
|    e | 2    |
|    f | 3    |
+------+------+

Assume that we don't exactly knew how many different types and number of column, so can you kindly guide me how to deal with this problem? Oh should I solve it in level programming language not the sql? I use php on Symfony2.

Thanks in advance,

it looks like your first table have three types storing the same kind of data.. which is not a good database practice...Read about Normalization..for start check this http://stackoverflow.com/questions/1258743/normalization-in-mysql — Dinesh, Apr 19 '13 at 16:19
elaborating on Dinesh comment: I would say you would have to create a table "types" and then create keys from your "data"-table to the "types"-table. as Dinesh says - it not good practice to store same data at several places (but sometimes it actually would be necessary for optimizing speed though) — bestprogrammerintheworld, Apr 19 '13 at 16:25
Since you say you are trying to learn, I've heard good things about the books "Database Design for Mere Mortals" and "Teach Yourself SQL in 10 Minutes". — Dan Bracuk, Apr 19 '13 at 16:29

score 8 · Answer 1 · answered Apr 19 '13 at 16:17

SELECT  type, COUNT(*) count
FROM    
    (
        SELECT  type1 type FROM data
        UNION ALL
        SELECT  type2 type FROM data
        UNION ALL
        SELECT  type3 type FROM Data
    ) AS subquery
GROUP   BY type

SQLFiddle Demo

OUTPUT

╔══════╦═══════╗
║ TYPE ║ COUNT ║
╠══════╬═══════╣
║ a    ║     4 ║
║ b    ║     2 ║
║ c    ║     4 ║
║ d    ║     3 ║
║ e    ║     2 ║
║ f    ║     3 ║
╚══════╩═══════╝

Learning a lot from people like you. Btw, what is the meaning of "type" in this line :- SELECT type1 type FROM data . Is it an alias ? — Vivek Sadh, Apr 19 '13 at 16:22

score 1 · Answer 2 · answered Apr 19 '13 at 16:30

1

An approach that should only require one scan of the data table:

select type, count(*) from
(select case t.typeno
            when 1 then d.type1
            when 2 then d.type2
            when 3 then d.type3
        end type
 from (select 1 typeno union all select 2 typeno union all select 3 typeno) t
 cross join data d
) sq
group by type

answered Apr 19 '13 at 16:30

1

I tried to compare the execution plan between yours and @JW and found out that you have more lines of execution plan than him http://www.sqlfiddle.com/#!2/bbf1f1/5 – Skinny Pipes Apr 19 '13 at 17:22
@SkinnyPipes: Yes - only one of which accesses the table (compared to three for JW's execution plan). Unless the actual data table is very large, this won't make much difference either way (but if it is a large table, I would expect this query to perform better). – Apr 19 '13 at 17:38

Sql statement query count data

2 Answers2