0
$\begingroup$

data = Table[SQLSelect[conn, "All", {"col1", "col2"}, SQLColumn["col1"] == n && SQLColumn["col2"] == o], {n, 1, 8}, {o, 21, 30}];

This results in selecting the range of data in each column specified. What I'd really like to do is get a count of each value in col1 for each value in col2. As it is, I will get:

{1,21},{1,22},{1,23},{1,24},{1,25},{1,26},{2,27},{1,23}...

If I use the complete range of values I just cull col1 and col2 from my original table. What I have in mind is a table, per this example code, 8 rows x 9 columns that shows the count of all the times 1 and 21 appear together, then all the times 1 and 22 appear together up to 30, then how many times 2 and 21 appear and so on.

I want to end up with a table that looks something like this (row and column headers can be ignored):

     21  22  23  24  25  26  27  28  29  30
1    1   1   2   7   12  30  47  80  91  112
2    1   1   3   12  18  48  59  118 133 151

and so on...

These SQL symbols are tricky in that they react strangely to variable manipulations.

Any suggestions would be appreciated.

Improve this question
$\endgroup$
2
  • $\begingroup$ Search de docs for Tally[] $\endgroup$
    – Dr. belisarius
    Mar 20, 2014 at 19:20
  • $\begingroup$ It might work if I could use that command directly on the table. I have a table with 9 columns and over 25,000,000 rows. I need to summarize statistics from this. It's food for thought. Thanks! $\endgroup$
    – Sinistar
    Mar 21, 2014 at 18:17

2 Answers 2

Reset to default
2
$\begingroup$

You will do yourself a service by doing as much as you can in SQL. In this case you can use

SELECT A, B, COUNT(*)
FROM Table1
GROUP BY A,B

with SQLExecute. After that you have to use GatherBy to group them by the first element to get the kind of format you're looking for. But it's pretty easy.

So for this kind of query I would recommend asking/searching at Stackoverflow first for SQL based solutions.

I generated some test data like this:

data = Transpose[{RandomInteger[{1, 8}, 1000], RandomInteger[{21, 30}, 1000]}];

And put up an SQL fiddle to demonstrate the SQL query in action.

fiddle

Improve this answer
$\endgroup$
17
  • $\begingroup$ I will give it a try. $\endgroup$
    – Sinistar
    Mar 21, 2014 at 17:50
  • $\begingroup$ The count is doubled. It appears to be counting col1 AND col2 in the total. I only want a tally of how many times col1 is a number as WHEN col2 is a certain value. I apologize if my original question wasn't clear. I guess I can just divide by two, but it concerns me that I do not have data integrity. $\endgroup$
    – Sinistar
    Mar 21, 2014 at 18:14
  • $\begingroup$ I am a novice at SQL and was hoping to avoid using it with MM, but MM cannot swallow by data set since it just doesn't handle large files well at all. If I change COUNT(*) to COUNT(col1) I am guessing I will get the desired result. I'll try it and comment again. $\endgroup$
    – Sinistar
    Mar 21, 2014 at 18:23
  • $\begingroup$ Changing the COUNT(col1) above is still counting everything twice. I need to keep things as simple as possible without having a workaround (remembering to divide by 2) in every piece of code that references this summary. Advice anyone? $\endgroup$
    – Sinistar
    Mar 21, 2014 at 18:28
  • $\begingroup$ @Sinistar I don't understand what you mean by counting it twice, there are several uneven numbes in the count column. This cannot happen if you count the same result twice. $\endgroup$
    – C. E.
    Mar 21, 2014 at 22:35
0
$\begingroup$ 
SELECT A, B, COUNT(A) OVER(PARTITION BY B) AS 'Count'
FROM DB.dbo.Table1
GROUP BY A, B
ORDER BY A, B

This is the query needed for Microsoft SQLServer 2012. This should work in versions back to SQL Server 2005, but was not tested. This correctly counts instances in the table where one value occurs with another value in a column. This would be similar in MM to using Position with Extract but this is easier, particularly because MM apparently chokes on files over 1GB so the database is pretty much required.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.