Solved

GROUPING results on a single row

Posted on 2014-10-18
6
153 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now