Who knows SQL?

Water Cooler
#1

SELECT *

FROM TableName

GROUP BY ColumnName

====

All fields in the SELECT statement must be included in the GROUP statement? I want to group by ColumnName and sum everything else. How do I do this without listing all the fields explicitly in the SELECT statement?

#2

if you are just browsing the data, distinct is often better than group by.

if you are actually producing a report, then look at user_tab_cols (sybase syscolumns) and build the sql dynamically.

dont you have a lacky to do this?

#3

Ok - need to research more. I never actually learned SQL.

Not sure if I am senior enough to have “lackies”, but the programming people who might qualify are doing something else…

#4

Quick, someone un-ban Blake so he can be of use!

#5

Type the column names that you want to display instead of *.

Select Column Name1, column Name 2 from Table 1 group by Column Name 1

#6

^ Agree. You can not group by *

Select

Column1

, Column2

, Column3

, SUM(Column4) [Name for This Sum]

from

TABLENAME

Group by

Column1

, Column2

, Column3

You have to group by all non-aggregated columns.

#7

The problem is kind of unique. I have a table whose column names can change from day to day. Furthermore, there are many, many columns. So, it is not practical to list all the column names, and I tried to cheat and use “*”. Probably, I need to figure out how to dynamically create the query, or more likely, I will just dump everything into my computer and use some method that I do know…

#8

Blake could come in handy here.

#9

ZIPS LMAO

#10

FTFY

#11

What’s “ZIPS LMAO”?

(I know what LMAO stands for. And looked up FTFY. I am old.)

#12

You should have urbandictionary.com in your favorites when posting on this site.

#13

^ not accessible at work

#14

Really? Surprised. It’s SFW.

#15

I’ve always avoided using SQL to a fault. I’ve been trying to improve my Excel, Access, PowerPivot skills. But SQL seems valuable to learn – so maybe it’s worth struggling with.

#16

^Access uses SQL so really learning one is learning the other. I have a general sense of it and if I need to do something I can usually review a book and figure it out. The thing that keeps me from using it more is that no one else in my group uses it. If it’s just me using it, then operational risk people will come down on us for not having a back up.

The benefit of a databaase (whether SQL or some other) is that you don’t have to read all the data into memory at once. So if I’m doing an analysis of 5000 stocks and a bunch of data points for each one over time, I don’t have to worry about my computer running out of memory if I start doing a bunch of operations on everything. If you’re not worried about memory, you can usually hack together something in excel that can approximate whatever it is that you could have used a proper database for.

#17

Kind of. Nothing like reading about Cleveland Steamers or Jelly Doughnuts at work.

#18

I will be honest, i don’t think it is possible with SQL, but it will take literally 10-15 lines of PL-SQL to do it.

#19

It seems to me that maybe the answer is to load the full table into memory and do the addition in the code that receives the data from SQL, like Python, Java, or even C++. If you aren’t going to get a zillion rows, that may be your best solution.

That was my initial response, but I held back because if someone knew how to do it in SQL, I wanted to see how to do it.

I’ve relearned and forgotten SQL so many times I don’t know if I should be embarrassed, or proud…

#20

Rant - wouldn’t it be more helpful if someone just posted the expansion of the acronym?

No need to “lmgtfy” it.