Solved

GROUPING results on a single row

Posted on 2014-10-18
6
158 Views
Last Modified: 2014-10-19
I have the following query

SELECT  a.id,a.name,
        a.created_by,
        a.published,
        a.type,
            IF (_tour_.period = 1, TRUE, FALSE) AS p1,
            IF (_tour_.period = 2, TRUE, FALSE) AS p2,
            IF (_tour_.period = 3, TRUE, FALSE) AS p3,
            IF (_tour_.period = 4, TRUE, FALSE) AS p4,
            IF (_tour_.period = 5, TRUE, FALSE) AS p5,
            IF (_tour_.period = 6, TRUE, FALSE) AS p6,
            IF (_tour_.period = 7, TRUE, FALSE) AS p7,
            IF (_tour_.period = 8, TRUE, FALSE) AS p8,
            IF (_tour_.period = 9, TRUE, FALSE) AS p9,
            IF (_tour_.period = 10, TRUE, FALSE) AS p10,
            IF (_tour_.period = 11, TRUE, FALSE) AS p11,
            IF (_tour_.period = 12, TRUE, FALSE) AS p12,
            IF (_tour_.period = 13, TRUE, FALSE) AS p13,
        _type_.type AS `_type_type`,
        _tour_.manager AS `_tour_manager`,
        _tour_.accompanied_by AS `_tour_accompanied_by`,
        _tour_.local_manager_agreed AS `_local_manager_agreed`

FROM p17oj_rtiptours_locations AS a

LEFT JOIN `p17oj_rtiptours_locationtypes` AS _type_
ON _type_.id = a.type

LEFT JOIN `p17oj_rtiptours_tours` AS _tour_
ON _tour_.location = a.id

WHERE (a.created_by = 40 OR 1)
AND (a.published = 0 OR a.published = 1 OR a.published IS NULL)

GROUP BY a.type,a.name

ORDER BY a.type,a.name,a.ordering asc

Open in new window


Which sort of works.. however lets say I have three tours for one location, it only shows the the search result.

NAME       P1   P2   P3  .... P12
BRISTOL   1     0     0         0

Open in new window


If I take out the line GROUP BY a.type,a.name then I can see all three results.

NAME       P1   P2   P3  .... P12
BRISTOL   1     0     0         0
BRISTOL   0     1     0         0
BRISTOL   0     0     1         0

Open in new window


I somehow need to alter this MYSQL so that if a location has multiple results the output will be

NAME       P1   P2   P3  .... P12
BRISTOL   1     1     1         0

Open in new window


I suspect there is a DISTINCT needed somewhere in there but when I try to use it I get MYSQl errors, but any help greatly appreciated for the last step of this query
0
Comment
Question by:mvwmail
  • 3
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40388836
So something like replacing the true/false with 1/0 and summing the rows

SUM(IF (_tour_.period = 1, 1, 0)) AS p1,
0
 
LVL 3

Author Closing Comment

by:mvwmail
ID: 40388870
Absolutely beautiful, still not sure in my head why that made all the difference, but it did
0
 
LVL 3

Author Comment

by:mvwmail
ID: 40388871
Here is the final code, altered to meet Gary's recommendation which worked beautifully

SELECT  a.id,a.name,
        a.created_by,
        a.published,
        a.type,
            SUM(IF (_tour_.period = 1, 1, 0)) AS p1,
            SUM(IF (_tour_.period = 2, 1, 0)) AS p2,
            SUM(IF (_tour_.period = 3, 1, 0)) AS p3,
            SUM(IF (_tour_.period = 4, 1, 0)) AS p4,
            SUM(IF (_tour_.period = 5, 1, 0)) AS p5,
            SUM(IF (_tour_.period = 6, 1, 0)) AS p6,
            SUM(IF (_tour_.period = 7, 1, 0)) AS p7,
            SUM(IF (_tour_.period = 8, 1, 0)) AS p8,
            SUM(IF (_tour_.period = 9, 1, 0)) AS p9,
            SUM(IF (_tour_.period = 10, 1, 0)) AS p10,
            SUM(IF (_tour_.period = 11,1, 0)) AS p11,
            SUM(IF (_tour_.period = 12, 1, 0)) AS p12,
            SUM(IF (_tour_.period = 13, 1, 0)) AS p13,
        _type_.type AS `_type_type`,
        _tour_.manager AS `_tour_manager`,
        _tour_.accompanied_by AS `_tour_accompanied_by`,
        _tour_.local_manager_agreed AS `_local_manager_agreed`

FROM p17oj_rtiptours_locations AS a

LEFT JOIN `p17oj_rtiptours_locationtypes` AS _type_
ON _type_.id = a.type

LEFT JOIN `p17oj_rtiptours_tours` AS _tour_
ON _tour_.location = a.id

WHERE (a.created_by = 40 OR 1)
AND (a.published = 0 OR a.published = 1 OR a.published IS NULL)

GROUP BY a.type,a.name

ORDER BY a.type,a.name,a.ordering asc

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 58

Expert Comment

by:Gary
ID: 40388889
Before the rows are grouped the value of each column in each grouped row is summed so you will either get 0 or 1+
I should have said the value maybe 1 or more so you may need to amend your code to take that into account if it is only currently checking if it's 1 or 0
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40389528
WARNING:

You are using MySQL's "extension" to GROUP BY
ref: http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

This is a most peculiar extension as it allows "approximation"* of the results and many experts recommend NOT allowing it (see the reference to change the setting)

In the meantime please recognize that when you use GROUP BY, ALL the "non-aggregating" fields should be specified, like this:
GROUP BY  a.id,a.name,
        a.created_by,
        a.published,
        a.type,
        _type_.type AS `_type_type`,
        _tour_.manager AS `_tour_manager`,
        _tour_.accompanied_by AS `_tour_accompanied_by`,
        _tour_.local_manager_agreed AS `_local_manager_agreed`

Open in new window



*
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause.  ....
However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.
The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.
same reference
0
 
LVL 3

Author Comment

by:mvwmail
ID: 40389879
Appreciate the extra feedback from all. Noted and implemented

Thanks Gary and PortletPaul
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 31
mysql date time 14 33
How do I fix this UPDATE error? 7 24
CSV How to add columns based on existing column(s)? 20 16
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question