Solved

GROUPING results on a single row

Posted on 2014-10-18
6
159 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 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