Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

multiple Select doesn't work.

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Guys,

I get this message:

...[SELECT - 0 row(s), 0.000 secs] [Error Code: 1248, SQL State: 42000] Syntax error or access violation message from server: "Every derived table must have its own alias"


This works:



this (below) doesn't work. I want to SUM the content in all D's


 
Michael Matola
whippersnapper
Ranch Hand
Posts: 1821
4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this (below) doesn't work. I want to SUM the content in all D's

Your outer select needs a GROUP BY CITY too, in order to make this approach work:

select city, SUM(D1+D2+D3+D4+D5) as DS from ( ... ) GROUP BY CITY

But, as I'll show below, you can do this without the inline view.

First though, get rid of all those DISTINCTs:

count(distinct case when type = 'D(1)' then '1' end ) "D1"

Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:

select count( distinct 'constantExpression' ) from all_tables

I'm guessing that's not what you intend.

---

Ok, back to easier ways to sum the Ds. You have numerous syntax choices, stll using case when...end.



Or,



Or,



Or, what I proposed in the original thread on this topic



---

Now, having said all that, using case when...end is great for throwing together ad hoc queries based on codes. But the fact that you want to report on "all the Ds" suggests to me that you've imbedded logic into your codes: D(1), D(2), T(1), T(2), etc.

A classy way of reporting on data like this is to encode these relationships into a table and join to that table in your query:



Then you use the values of reportingType instead of building those groups in the query.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks Michael,

"Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:"

Yes, I did that intentionally.

I got your answer and it helped me.
got it.

thanks a lot
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic