SQL Question

In Access:

SELECT Table2.category_ID AS category, count(*) AS incidents, sum(Table2.dead) AS dead, avg(Table2.dead) AS [avg]

FROM Table2

GROUP BY category_ID;

returns a table with the category table correctly looked up:

In Excel, I use Data->Get Data to connect to that query, and I get this:

Instead of using the lookup table for Category, I get the lookup index.  Any idea why?

Today's "Stroke Damaged My Brain" SQL Question

 The query:

SELECT int(year/10)*10 AS decade, count(*) AS incidents, sum(incidents.dead) AS dead
FROM incidents
WHERE ([cause]='MI' or [cause]='MI?')
GROUP BY (int (year/10)*10);

This produces three columns:

 

decade	incidents	dead
1660	1	2
1800	1	8
1860	7	27
1870	4	19
1880	12	55
1890	19	78
1900	43	183
1910	21	83
1920	7	31
1940	2	18
1960	2	21
1970	6	27
1980	8	45
1990	9	48
2000	4	55
2010	11	170
2020	1	5

You will notice that there are no rows for the decades 1810 through 1859.  This is because there are no incidents where the cause was clearly or likely mental illness.  (If this seems odd, schizophrenia rates in Britain roughly octupled from the17th to 19th centuries, and similar dramatic increases happen in America over this same period.  One possible cause is cats going from mousekillers in barns into homes as pets with toxoplasmosis in cat feces, as one possible environmental trigger.)  So how do I get rows with 0 incidents into that query?

I tried:

SELECT int(year/10)*10 AS decade, count(*) AS incidents, sum(Table2.dead) AS 'dead'
FROM Table2
WHERE ((cause_ID=15) or (cause_ID=16))
UNION
SELECT int(year/10)*10 AS decade, count(*) AS incidents, sum(Table2.dead) AS 'dead'
FROM Table2
WHERE ((cause_ID<>15) and (cause_ID<>16))
GROUP BY (int (year/10)*10) ;

but the complaint is that "Your query does not include the specified expression int(year/10)*10 as an aggregate function."

Thanks to all.  This mostly fixes it:

SELECT int(year/10)*10 AS decade, count(*) AS incidents, sum(Table2.dead) AS 'dead'
FROM Table2
WHERE ((cause_ID=15) or (cause_ID=16))
GROUP BY (int (year/10)*10)
UNION SELECT int(year/10)*10 AS decade, 0, 0
FROM Table2
WHERE ((cause_ID<>15) and (cause_ID<>16))
GROUP BY (int (year/10)*10);

However, if the first SELECT has matches, the UNION gives me two rows before DECADE, and some decades are still missing:

incidents mentally by decade

decade	incidents	'dead'
1650	0	0
1660	1	2
1680	0	0
1690	0	0
1720	0	0
1750	0	0
1780	0	0
1800	0	0
1800	1	8
1820	0	0
1830	0	0
1840	0	0
1850	0	0
1860	0	0
1860	7	27
1870	0	0
1870	4	19
1880	0	0
1880	12	55
1890	0	0
1890	19	78
1900	0	0
1900	43	183
1910	0	0
1910	21	83
1920	0	0
1920	7	31
1930	0	0
1940	2	18
1950	0	0
1960	0	0
1960	2	21
1970	0	0
1970	6	27
1980	0	0
1980	8	45
1990	0	0
1990	9	48
2000	0	0
2000	4	55
2010	0	0
2010	11	170
2020	0	0
2020	1	5

 I think instead of a UNION I need to only do the second SELECT if there is no record from the first SELECT.

Switching to Access Probably a Bad Idea

Word cannot link to Access like it does to Excel.  Excel can get data from Access, but in a not very useful way.  My thought was to import into Excel, and link to Word.

The Access query produces:

category dead

'dead'	'Category'
671	FAM
357	FAMNONRES
15	PRIV
5970	PUB
169	RES
124	SCHOOL
18	UNKNOWN
165	WORK
12	WORSHIP

but imported into Excel all the Category names turn into numbers:

SumOfdead	cause
6	4
3	5
2	8
3	10
11	18
3	20
3	24

even though I imported them as text.  I think Access normalized all the category names.  It is almost like Access, Word, and Excel were written by separate corporations.

And no, I cannot write a book in SQL queries.

Figured it out.  I imported from the old database, not the new one.

It is clumsy.  Link from Excel to Access query results.  Create graph from data in Excel.  Link from Word to Excel objects.

SQL Question

SELECT count(*) as 'ax mass murders'
WHERE (incidents.ax=1);

Access rejects this as "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

Missing "FROM incidents"

The count seems too low.

Trying to sum dead by decade was hard but I solved it:

select int(year/10)*10, sum(incidents.dead) as dead from  incidents group by (int (year/10)*10);

Finding Duplicate Rows For Multiple Columns

I had occasion recently to look for some SQL to find rows in a table where at least two columns must be unique: as an example, a table where customer_id and product_id must be unique.  You can have multiple customers with the same product_id but only one row for a particular customer can have that product_id.  (The database should have made the combination of  customer_id and product_id unique, but it wasn't done, so there were a few duplicate rows.)

SELECT  customer_id,  product_id, COUNT(*) 

FROM cust_table GROUP BY  customer_id,  product_id  HAVING (COUNT(*) > 1) 

Useful SQL Tricks

At least, for Informix's warped version of SQL. I needed to find all the tables that referenced a particular row in an existing table, because the row I created in one table could not be deleted until all the references went away. The following SQL will give you a list of all tables that have a column named 'ofndr_num':


SELECT TRIM(t.tabname) AS table
FROM "informix".systables AS t, "informix".syscolumns AS c
WHERE t.tabid = c.tabid AND t.tabtype = 'T' AND c.colname = 'ofndr_num' AND t.tabid >= 100 ORDER BY t.tabname, c.colno ;