How to show multiple values of a single field in a single row in a single query?

Question

Table 1:Domain Link Result
======================================================================
||Column1(words)     ||Column2(links)    ||Column3(frequency)       ||
======================================================================
    1                   1                   Any Number
    2                   1                   Any Number
    3                   1                   Any Number
    4                   1                   Any Number
    1                   2                   Any Number
    2                   2                   Any Number
    3                   2                   Any Number
    4                   2                   Any Number


Table 2:Sub Link Result
======================================================================
||Column1(words)     ||Column2(sublinks)    ||Column3(frequency)    ||
======================================================================
    1                   a                   Any Number
    2                   b                   Any Number
    3                   c                   Any Number
    4                   d                   Any Number
    1                   e                   Any Number
    2                   f                   Any Number
    3                   g                   Any Number
    4                   h                   Any Number

And so on.

In the above scenario user entered 4 words and 2 domain links. Now the frequency of 4 keywords is calculated on domain links as well sublinks and stored in separate tables as shown above. I want an aggregate result like below:

Table 3:Final Result
==================================================================================
||Column1(words)     ||Column2(Domain links)    ||Column3(Total frequency)      ||
==================================================================================
Row1:    1              1                    Total of frequency in both tables
                        2                    for word "1"   
----------------------------------------------------------------------------------
Row2:    2              1                    Total of frequency in both tables
                        2                    for word "2"   
----------------------------------------------------------------------------------
Row3:    3              1                    Total of frequency in both tables
                        2                    for word "3"   
----------------------------------------------------------------------------------
Row4:    4              1                    Total of frequency in both tables
                        2                    for word "4"   
----------------------------------------------------------------------------------

I tried the following query in MySQL:

SELECT t.`keyword`, t.`link` SUM( t.`frequency` ) AS total
FROM (

SELECT `frequency`
FROM `domain_link_result`
WHERE `keyword` = 'national'
UNION ALL
SELECT `frequency`
FROM `sub_link_result`
WHERE `keyword` = 'national'
)t GROUP BY `keyword`

But in Column 2 of the final result I get only first link instead of two links for row 1. How can I get both links or any number of links entered by user in a single row ?

Words and Links have VARCHAR as type and frequency has INT type.

bgusach · Accepted Answer · 2013-03-28T09:45:13.027

2

If you want to collapse several rows into one and still be able to see the information, you have to use GROUP_CONCAT

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

This outputs the collapsed values separated by commas, i.e.: a string. In your programming language you can split this string if you need individual values.

Your query would look somehow like this

SELECT keyword, GROUP_CONCAT(links), SUM(frequency)

FROM (subquery)

GROUP BY keyword

Which would output something like this:

==================================================================================
||Column1(words)     ||Column2(Domain links)    ||Column3(Total frequency)      ||
==================================================================================
Row1:    1              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row2:    2              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row3:    3              1,2                     sum of freq.
----------------------------------------------------------------------------------
Row4:    4              1,2                     sum of freq.

EDIT: Extra help for your query

Your query looks a little bit confusing to me. Try with a JOIN approach:

SELECT domain_link_results.word AS word,
       GROUP_CONCAT(domain_link_results.links) AS domain_links,
       domain_link_results.frequency + sub_link_results.frequency AS total_frequency

FROM domain_link_results

INNER JOIN sub_link_results
        ON domain_link_results.word = sub_link_results.word

WHERE domain_link_results.word = "national"

GROUP BY domain_link_results.word

On the other hand, it might be better to have all the links in the same table, and an extra field to determine if it's a domain link or a sublink. Without knowing more about your system it is hard to say.

edited Mar 28 '13 at 09:45

answered Mar 26 '13 at 20:46

bgusach

14,527
14
51
68

In the column 2 of Final result, I am getting Sub Links instead Domain Links after using your approach. Is there any way I can only select Domain Links exclusively from the subquery result? – SilentAssassin Mar 27 '13 at 06:46
There is syntax error coming from the second argument of `SUM` function. Can `SUM` function be given two arguments like that ? – SilentAssassin Mar 28 '13 at 06:13
Oops my bad. I fixed it. Try again. – bgusach Mar 28 '13 at 09:45
See [this](http://pastebin.com/i8P1bXXP). This is my query with actual table names and instead of `word` I am joining them on same `task_id`. The query you have written does not achieve what I want. I want to add the SUM of frequencies of both the tables. Saying it again, at first individual frequencies of both the tables are summed up then 2 sum values of both tables have to be added again to get the total frequency. The current query is not fetching that as there is no summation of frequencies of individual tables. Hope you understood what I am trying to say. – SilentAssassin Mar 28 '13 at 10:43
Uff, it's hard without having the actual data. Might it be that you want to include an extra field with `SUM(domain_link_results.frequency + sub_link_results.frequency)` ? it would represent the total frequency (link + sub_link) of the group created by `GROUP BY` – bgusach Mar 28 '13 at 11:34
Okay I am getting my result using the `UNION ALL` approach. But when I write query in my PHP code, the GROUP CONCAT SEPARATOR which I am giving as `\n`(newline) is not working. Any ideas why is that happening ? – SilentAssassin Mar 28 '13 at 12:35
How are you writing the quotes? PHP is a litte bit messy with simple a double quotes. – bgusach Mar 28 '13 at 12:46
Yeah I will dig into that a bit and get back to you later. – SilentAssassin Mar 28 '13 at 13:20
Take this into account http://stackoverflow.com/questions/4238433/php-how-to-create-a-newline-character. Anyway, if you want to split that field into an array in PHP, use directly commas and explode the string. – bgusach Mar 28 '13 at 13:35

How to show multiple values of a single field in a single row in a single query?

1 Answers1