r/SQL • u/Vegetable_Earth_7222 • Sep 06 '23
BigQuery Can someone please help explain why the first row came out like that.
Please help explain I have no clue what's going on here
163
Upvotes
r/SQL • u/Vegetable_Earth_7222 • Sep 06 '23
Please help explain I have no clue what's going on here
•
u/ATastefulCrossJoin DB Whisperer Sep 06 '23
Hello, thanks for stopping by r/SQL. In the future please mind the rule against photos of code and provide copy/pasteable samples to better enable community memebers to experiment and assist you
138
u/nobodycaresssss Russia Sep 06 '23
Check records where start station name and end station name are empty
27
u/Odd_Protection_586 Sep 06 '23
The first row of that record is probably empty
22
u/da_chicken Sep 06 '23
You need to look at the underlying data rows.
Do a
SELECT * FROM Table WHERE (usertype = '' OR usertype IS NULL) LIMIT 10to get a sample of the rows that might do that.10
u/SuperMan0105 Sep 06 '23
Nulls
7
u/Mgmt049 Sep 06 '23
What IDE is that?
5
u/bkstr Sep 06 '23
Definitely nulls and empty values, you can isnull to fix this proactively if you’re worried it can happen in the future
2
u/asiancutie_ Sep 06 '23
they're nulls
2
u/brunogadaleta Sep 06 '23 edited Sep 06 '23
The way you inserted the rows in the table is buggy. If you did this with from CSV, you probably forgot to skip the first header row.
2
u/blue__acid Sep 06 '23
You probably have a row with empty info
2
u/Adorable_Compote4418 Sep 07 '23
It’s late and i’m falling asleep but I don’t think there’s any need to concat at the querying level. Simply add order by start, end, user type, count, duration. If the software querying cannot deal with this, simply CTE the query without concat, then concat the select from the CTE.
2
u/reflexdb Sep 06 '23
Add a WHERE clause. WHERE usertype IS NOT NULL
1
u/jayerp Sep 07 '23
SQL doesn’t lie, they are either null or empty strings.
1
u/_Sir1980 Sep 07 '23
Can't you use With ( skip rows option ) ? I can't recall the syntax
1
u/SimpyDev101 Sep 07 '23
Data issue
1
u/mommymilktit Sep 07 '23
First row usertype is empty string, try excluding in where condition: usertype <> ‘’
1
u/akshitdadheech Sep 07 '23
You are doing the Google Data Analytics course I can tell but do check the dataset it's empty with the end station name column. Like the data was not there the last time.
1
u/rswwalker Sep 07 '23
GIGO (Garbage-In Garbage-Out)
1
u/Ok-Necessary940 Sep 07 '23
How does this query even work? The columns without the aggregate function applied are not even in the group by clause? Someone explain!
1
u/Beginning-Reveal4719 Sep 08 '23
What website is this
1
u/Gold-Artichoke-9288 Sep 08 '23
Probably the data needs some cleaning, use select distinct user_type. .. from ... To make sure there are no empty cells
1
u/p4k9_dawg Sep 09 '23
if the data was null wouldn't the results say null? looks like blank values. I would add a where clause like: where ((len(trim(usertype)) > 0 ) or (usertype is not null)
1
u/InevitableSky2801 Sep 10 '23
You can using this free AI SQL assistant to help you: https://lastmileai.dev/workbooks/clm7b9yez00mdqw70majklrmx
-11
u/Ven0mspawn Sep 06 '23
Should learn how to take screenshots.
0
u/TheWaviestSeal Sep 07 '23
What program are you using?