-1

I've always been terrible at queries since db class and now I need to extract some data for work. Here is a sample of how the data I was given was formatted. I already transposed it so Workbench would import.

sample-data-img

What I need to query for are all of the non-zero values so I can generate a report that has itemName, date used, and # of times used.

I did a COUNTIF in Excel and there are apparently 90 non-zero values in the data. The query SELECT * FROM myData WHERE "insert-any-column" <> 0 returns the entire table.

Any hints towards how to approach this would be awesome.

EDIT: Schema is exactly what you see in the sample image above.

Table: myData

Columns: itemName, 1/1/17, 1/2/17, etc.

raccoon_nine
  • 43
  • 1
  • 8
  • Where *what* `<> 0`? You haven't specified a column. – ceejayoz Nov 20 '17 at 18:04
  • Sorry, meant to put an "insert-any-column" != 0, I tried for any column and still get back the entire table – raccoon_nine Nov 20 '17 at 18:06
  • There's no "across multiple columns" `WHERE` clause in MySQL. You'll have to do `WHERE (foo <> 0 OR bar <> 0 OR meh <> 0)`. – ceejayoz Nov 20 '17 at 18:07
  • (and make sure the data you're searching is numeric - `<>` against a string won't get you the results you think) – ceejayoz Nov 20 '17 at 18:08
  • Yes, you're right? I tried many different columns and still get the entire table back – raccoon_nine Nov 20 '17 at 18:09
  • Please add your table schema to the question, then. – ceejayoz Nov 20 '17 at 18:10
  • That's not a table schema. Show us [the output](https://stackoverflow.com/questions/1498777/how-do-i-show-the-schema-of-a-table-in-a-mysql-database) of `SHOW CREATE TABLE myData`. I'm particularly interested in the data *types* of your columns. If your column names are literally stuff like `1/1/17` you're also probably setting things up very *wrong*. – ceejayoz Nov 20 '17 at 18:25
  • That's what I just gave you? I assumed the data type was self explanatory. itemName varchar(255) NOT NULL, 1/1/17 int(11) DEFAULT NULL, etc. I was given the data this way and I'm looking to see if anyone knows a way to go around the terrible formatting before I go reformatting the data myself. – raccoon_nine Nov 20 '17 at 18:40

1 Answers1

0
SELECT * FROM myData WHERE your_column <> 0

Please note that you may need to use

SELECT * FROM myData WHERE your_column IS NOT NULL
Luis Martins
  • 1,572
  • 12
  • 11