Solved

GROUPING results on a single row

Posted on 2014-10-18
6
161 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

632 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