MySQL How To Use Main Select Value As Subquery Argument?

I am trying to 开发者_Python百科construct a query that is a bit more complicated than anything I've done in my limited experience with databases.

TABLE:

id - data - type - data3
1 - hello - 1 - 1
2 - goodbye - 1 - 1
3 - goodbye - 1 - 2
4 - goodbye - 2 - 1
5 - hello - 2 - 1

The goal is to do 4 things:

1. GROUP the results by "data", but only return one result/row of each data type.
2. COUNT the total number of each "data GROUP and return this number.
3. Do this for both "type"=1 and "type"=2, though I only need each "data" GROUP item once.
4. the ability to sort results based on each SELECT item.

So the final result returned should be (sorry to be confusing!):

data, COUNT(data["type"]=1), COUNT(data["type"]=2 AND data["data"] = data)

So, for the sample table above, desired results would be:

loop 1 - hello, 2, 1
loop 2 - goodbye, 3, 1

Then, ideally, I could sort results by any of these.

This is the query I was trying to construct before resorting to posting this, I don't think it's even close to being correct, but it may help illustrate what I'm trying to achieve a bit better:

SELECT 
  (
   SELECT `clicks_network_subid_data`, COUNT(*) 
   FROM track_clicks 
   WHERE `clicks_campaign_id`='$id' AND `clicks_click_type` = '1'
  ) AS keywords, 
  (
   SELECT COUNT(*) 
   FROM track_clicks 
   WHERE `clicks_campaign_id`='$id' AND `clicks_click_type` = '2' AND `clicks_network_subid_data` = keywords.clicks_network_subid_data 
  ) AS offer_clicks 
 GROUP BY keywords.clicks_network_subid_data 
 ORDER BY keywords.COUNT(*) DESC

I also need to do a JOIN on another table to grab one more piece of data, but I think I can handle that once I get this part figured out.

You can use an IF-function for this

SELECT `clicks_network_subid_data`, 
SUM(IF(clicks_click_type` == '1',1,0)) as keywords,
SUM(IF(clicks_click_type` == '2',1,0)) as offer_clicks,
FROM track_clicks 
GROUP BY clicks_network_subid_data
ORDER BY clicks_network_subid_data DESC

You can do this using GROUP BY:

SELECT data, COUNT(*) AS cnt FROM `table` GROUP BY type ORDER BY COUNT(*)

Ordering might become a little slow, as this is a calculated field, but if you don't have a large result set then you are good to go.

First of all your question is bit not clear , However, check this query . what I suspect is that you need count results in columns (single ) instead of rows .

select * , count(type_one) as t1_count , count(type_two) as t2_count from (
select  data,if(tmp.type=1,1,0) as type_one, if(tmp.type=2,1,0) as type_two      from  (
    select 1 as id , 'hello' as data , 1 as type , 1 as data3  union
    select 2 as id , 'goodbye' as data , 1 as type , 1 as data3 union
    select 3 as id , 'goodbye' as data , 1 as type , 2 as data3 union
    select 4 as id , 'goodbye' as data , 2 as type , 1 as data3 union
    select 5 as id , 'hello' as data , 2 as type , 1 as data3  
) tmp
) tmp2

group by tmp2.type_one ;

let me know if this works for you

cheers :)