Forums Register Login

multiple Select doesn't work.

+Pie Number of slices to send: Send
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


+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
Can you smell this for me? I think this tiny ad smells like blueberry pie!
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 957 times.
Similar Threads
SQL with multiple counts
double behaviour
Question on Pass By Reference
variable problem
Dan's Mock Question - Double Wrapper
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 07:53:23.