Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

GROUPING results on a single row

Posted on 2014-10-18
6
Medium Priority
?
162 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 2000 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 49

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 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