Selecting from multiple tables based on conditions

user00344
user00344 Member
in Magic ETL

I have the following code where I am pulling all data from one table, a. Then counting a column from a different table as a new column.  However, I am getting a syntax error message.  Can someone point me in the right direction?

 

select a.* 
,(Select count(DISTINCT b.Employee) as `Number of Distro` 
	from `transform_data_9` b
  	where Date_Format(a.date,'%Y-%m-%d') = b.`date` and a.date between b.`End Time` - b.`Start Time` and b.`department` = 'Distro')
from `transform_data_8` a

 

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    You need to give a name to the new calculated column, like so:

    select a.* 
    ,(Select count(DISTINCT b.Employee) 
    	from `transform_data_9` b
      	where Date_Format(a.date,'%Y-%m-%d') = b.`date` and a.date between b.`End Time` - b.`Start Time` and b.`department` = 'Distro') AS `Number of Distro`
    from `transform_data_8` a

     

    Let me know if you still have any issues,

    Valiant_Ronin

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you. 

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    You need to give a name to the new calculated column, like so:

    select a.* 
    ,(Select count(DISTINCT b.Employee) 
    	from `transform_data_9` b
      	where Date_Format(a.date,'%Y-%m-%d') = b.`date` and a.date between b.`End Time` - b.`Start Time` and b.`department` = 'Distro') AS `Number of Distro`
    from `transform_data_8` a

     

    Let me know if you still have any issues,

    Valiant_Ronin

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you. 

Categories