So I am running this query:

SELECT
response_1__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1

This is great and functioning fine. The problem is the reponse_1__c is actually 1 of 20 fields i need data on..so I was wondering how to use this query with unions to get all of the data in one query, but also have it separated by the response_1__c field or response_2__c , 3 , and so on.

I tried the following query but it just lumps the totals together with no easy way to distinguish the data..anyone have any ideas?

SELECT
response_1__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1

UNION

SELECT
response_2__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1

[.img]http://s17.postimg.org/4ozhx7vxb/DATA_PULL.png[./img]

As you can see, my main goal is to have that same data but in a cleaner, more organized format.

if i understand you correctly, you just want to distinguish between response_1_c and response_2_c, right?

just add another column in your select.

SELECT
'response_1_c' as key
,response_1__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1, 2

UNION

SELECT
'response_2_c' as key
,response_2__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1, 2

then slap an order by on top of that to keep it together.

so instead of:

tap dat ass, 9
kill it with fire, 15
no speak engrish, 1

you'll see:
reponse_1_c, tap dat ass, 9
response_2_c, kill it with fire, 15
reponse_2_c, no speak engrish, 1

This post was edited by carteblanche on Feb 26 2015 07:05pm

hey bro awesome response..can you elaborate on the bolded portion of setting it as key what that does from a high level?

new to sql but this would be incredibly ehlpful if it works.

also lost you with the slap an order by portion and everything after lol..

also the columns you are creating as keys will be 20 in total..is that a factor or no?

This post was edited by noflexzone on Feb 26 2015 07:17pm

im not entirely sure what you're asking for, so i took a guess. i think your problem is that after you union everything together, you no longer know which SELECT statement it came from, right? that's all it's doing.

when you say 20 columns...you're not really creating 20 columns right? it's just 2 columns (3 with my suggestion) and you will have 20 SELECT statements, right?

the order by is just to combine all the rows with the corresponding select. eg:

select * from (
SELECT
'response_1_c' as key
,response_1__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1, 2

UNION

SELECT
'response_2_c' as key
,response_2__c
, SUM(1)
FROM
sf_survey__c a
WHERE
survey_id__c = 1658015
GROUP BY 1, 2
) order by key

personally, i would consider calling this from non-sql instead of all the unions. but if it has to be done via sql, that's the way to do it.

that makes perfect sense. that's a great explanation and I completely understand. The main issue is the data and schema within the table that makes this such a pain in the ass. But with your query, it's 20 queries with easy to read data that i can export and read right away. I'm trying to build a query template because all surveys are going to come in under the same template. As I venture further and further in sql, i'd love to be able to reach out to you from time to time for advice. Would that be ok?

I'm trying to think what the best layout for this would be as my next step is to join in another table with customer properties and attributes to actually analyze the data.. if i were to do that I would just add on to that existing query ?

This post was edited by noflexzone on Feb 26 2015 07:43pm

When you have questions, create a topic. plenty of people here can help you out.



depends on the data. does the join have to occur before the union or can it occur after? if after, personally, i'd create a view with this monster union to make it easier to manage. also, if your join is n-to-1, then it can return many rows and ruins your format.

So we are now at that next step. I want to run the results against another table which needs to be joined. I PMed you my current template. I literally had 20 separate joins within the 20 select statements.

Looks something like this...is this the optimal way to do it? I want to also include more where statement criteria...what's the best methodology for doing that?

SELECT * FROM (
SELECT
'a.response_1__c' as KEY
,a.response_1__c
,SUM(1)
FROM
sf_survey__c a
LEFT JOIN odc_marketing.sf_opportunity b ON (a.id = b.id)
WHERE
a.survey_id__c = 1658015
GROUP BY 1, 2

UNION

SELECT
'a.response_2__c' as KEY
,a.response_2__c
,SUM(1)
FROM
sf_survey__c a
LEFT JOIN odc_marketing.sf_opportunity b ON (a.id = b.id)
WHERE
a.survey_id__c = 1658015
GROUP BY 1, 2