My quick google with the terms &quot;count unique values sqlite3&quot; landed me on this post. However, I was trying to count the overall number of unique values, instead of how many duplicates there are for each category. 

From Chris&#39;s result table above, I just want to know how many unique colors there are. The correct answer here would be four [4].

This can be done using
`select count(DISTINCT type) from table;`



A quick google gave me this: http://www.mail-archive.com/sqlite-users@sqlite.org/msg38339.html

    select type, count(type) from table group by type;

I need to know what methods people are using to distribute (internally) demo of iPad application wirelessly without having to manage UDID?

I do not need a solution like testflight or betabuilder, all these requires me to manage UDID in the apple provisioning profile portal.

I am asking this question because I am seeing another studio doing a much smarter way right now and I wish to know how they did it - With a link they gave to us, we can install their demo application from a simple HTML over the air, and a provisioning profile magically appears in my iPad. All this happened without me telling them the UDID of my iPad, and therefore definitely without them to recompile the project.

How did they do it?






How to distribute ios application wirelessly without managing UDIDs and recompilation

Though I know how to set a global key-binding in Emacs, I find it hard to even Google out the code for a local (minor-mode specific) key-binding. For instance, I have this code in my `.emacs`:

    ;; PDFLaTeX from AucTeX
    (global-set-key (kbd &quot;C-c M-p&quot;)
    		(lambda ()
    		  (interactive)
    		  (shell-command (concat &quot;pdflatex &quot; buffer-file-name))))

I don&#39;t want to set it globally. Is there a function like `local-set-key`?

Set custom keybinding for specific Emacs mode

I have a table:

    ref,type
    1,red
    2,red
    3,green
    4,blue
    5,black
    6,black

I want the result of a sqlite query to be:

    red,2
    green,1
    blue,1
    black,2

I think the hardest thing to do is find a question to match my problem?  Then I am sure the answer is around the corner....

:)

How can I count how many duplicates there are for each distinct value in sqlite?

I have a table:
<pre><code class="hljs language-rust">ref,type
1,red
2,red
3,green
4,blue
5,black
6,black
</code></pre>
I want the result of a sqlite query to be:
<pre><code class="hljs">red,2
green,1
blue,1
black,2
</code></pre>
I think the hardest thing to do is find a question to match my problem? Then I am sure the answer is around the corner....
:)

I&#39;m just learning to use SQLite and I was curious if such is possible:

1. Encryption of the database file?

2. Password protect opening of the database?


PS. I know that there is this &quot;SQLite Encryption Extension (SEE).&quot;, but according to the documentation, &quot;The SEE is licensed software....&quot; and &quot;The cost of a perpetual source code license for SEE is US $2000.&quot;

SQLite with encryption/password protection

I have a table with some records with fields like name, rating etc.

I first want to sort based on rating limiting results to 20 and then on this resultset want to further apply sort based on name.

I know to sort we need to use the query like 

    Select * from table order by rating Desc limit 20

but on this resultset how to apply another level of ordering? How can I combine these two sorts in one sqlite statement?


Multiple level ordering

I&#39;m making a shell script to export a sqlite query to a csv file, just like this:

     #!/bin/bash
    ./bin/sqlite3 ./sys/xserve_sqlite.db &quot;.headers on&quot;
    ./bin/sqlite3 ./sys/xserve_sqlite.db &quot;.mode csv&quot;
    ./bin/sqlite3 ./sys/xserve_sqlite.db &quot;.output out.csv&quot;
    ./bin/sqlite3 ./sys/xserve_sqlite.db &quot;select * from eS1100_sensor_results;&quot;
    ./bin/sqlite3 ./sys/xserve_sqlite.db &quot;.exit&quot;

When executing the script, the output apears on the screen, instead of being saved to &quot;out.csv&quot;. It&#39;s working doing the same method with the command line, but I don&#39;t know why the shell script fails to export data to the file.

What am I doing wrong?


Export from sqlite to csv using shell script

I really need a sqlite database file for studies.
I&#39;m creating an application in C# and I need a database with many tables, field, views, indexes, constraints and so on. And I don&#39;t want to create it myself using sqlite command line tool.

So I suppose may be someone can say where I can find such file, may be different applications or examples, or even can send me their own database file. I will appreciate any help.


Where I can find example of SQLite database file or dump of it?

I have the following structure: (Sorry for awkward names, it is because it is a sqlite database for my iPhone app which is not released yet)

    CREATE TABLE klb_log (
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      log_comment varchar(512)
    )

    CREATE TABLE klb_log_food_maps (
      uid integer,
      did integer,
      PRIMARY KEY (uid,did),
      FOREIGN KEY (uid) references klb_log(id) ON DELETE CASCADE,
      FOREIGN KEY (did) references klb_food(id) ON DELETE CASCADE
    )

    CREATE TABLE klb_food (
      id integer,
      description varchar(255),
      PRIMARY KEY (id)
    )

Is there a reason why the row in `klb_log_food_maps` is not removed when I delete a row in `klb_log`? 



ON DELETE CASCADE in sqlite3

Here is my query:

`SELECT COUNT(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0`

Here is the result:

&gt; Msg 8115, Level 16, State 2, Line 1
&gt;
&gt; Arithmetic overflow error converting expression to data type int.

The table has 4 billion rows. I don&#39;t expect this query to be fast, but after about 5mins, it fails with an overflow error. Is there a `COUNT` function for bigger data than int?

Thanks.

SQL COUNT overflow

In Javascript, I&#39;m trying to take an initial array of number values and count the elements inside it. Ideally, the result would be two new arrays, the first specifying each unique element, and the second containing the number of times each element occurs. However, I&#39;m open to suggestions on the format of the output.

For example, if the initial array was:

    5, 5, 5, 2, 2, 2, 2, 2, 9, 4

Then two new arrays would be created. The first would contain the name of each unique element:

    5, 2, 9, 4

The second would contain the number of times that element occurred in the initial array:

    3, 5, 1, 1

Because the number 5 occurs three times in the initial array, the number 2 occurs five times and 9 and 4 both appear once.

I&#39;ve searched a lot for a solution, but nothing seems to work, and everything I&#39;ve tried myself has wound up being ridiculously complex. Any help would be appreciated!

Thanks :)

Counting the occurrences / frequency of array elements

I would like some help with getting the size of an array inside an object: 

    var st = { &quot;itema&quot;:{...},&quot;itemb&quot;:[{&quot;id&quot;:&quot;s01&quot;,&quot;cd&quot;:&quot;c01&quot;,&quot;dd&quot;:&quot;d01&quot;,....}{&quot;id&quot;:&quot;s02&quot;,&quot;cd&quot;:&quot;c02&quot;,&quot;dd&quot;:&quot;d02&quot;,....}]}

How would you get a count of the objects inside `&quot;itemb&quot;` (in this case 2)?

Getting the size of an array in an object

I’m trying to count a particular word occurrence in a whole directory. Is this possible?

Say for example there is a directory with 100 files all of whose files may have the word “aaa” in them. How would I count the number of “aaa” in all the files under that directory?

I tried something like:

     zegrep &quot;xception&quot; `find . -name &#39;*auth*application*&#39; | wc -l 

But it’s not working.

How to count occurrences of a word in all the files of a directory?

The problem reduces to counting `\n` characters, so is there a function that can do it on a huge strings, since explode() wastes too much memory.

how to count new lines in a very big string?

Here I go with my basic questions again, but please bear with me.

In Matlab, is fairly simple to add a number to elements in a list:

    a = [1,1,1,1,1]
    b = a + 1

`b` then is `[2,2,2,2,2]`

In python this doesn&#39;t seem to work, at least on a list.

Is there a simple fast way to add up a single number to the entire list.

Thanks

Sum one number to every element in a list (or array) in Python

Say I have a simple function in MySQL:

    SELECT SUM(Column_1)
    FROM Table
    WHERE Column_2 = &#39;Test&#39;

If no entries in `Column_`2 contain the text &#39;Test&#39; then this function returns `NULL`, while I would like it to return 0. 

I&#39;m aware that a similar question has been asked a few times here, but I haven&#39;t been able to adapt the answers to my purposes, so I&#39;d be grateful for some help to get this sorted.

How do I get SUM function in MySQL to return &#39;0&#39; if no values are found?

I am building a query for a report with multiple IF conditions on the SUM. I am having problems with a multiple IF conditions on the SUM.

Here is the query:

    SELECT SUM(`totalamount`) AS Total, 
	SUM(`PayPalFee`) AS Fees,
	DATE(`TransactionDate`) AS `Day`, 
	SUM(IF(PaymentType = &quot;paypal&quot;, 1,0)) AS Paypal, 
	SUM(IF(PaymentType = &quot;check&quot;, 1,0)) AS Checks, 
	SUM(IF(PaymentType = &quot;credit card&quot;, 1,0)) AS CreditCard, 
	COUNT(*) AS Entries
     FROM my_table
     WHERE TransactionDate between &#39;2011-05-05&#39; AND &#39;2012-01-30&#39;
     GROUP BY day
     ORDER BY `day` ASC

This query works just fine. 

When I try to add the below conditional SUM statement:

     SUM(&#39;TotalAmount&#39;(PaymentType = &quot;credit card&quot;, 1,0)) AS CreditCardTotal,

This conditional IF statement fails out. 

I have a column called &#39;TotalAmount&#39; and a column called &#39;PaymentType&#39; I am looking to create a SUM of the credit card transactions by each day, a SUM of the checks transactions by each day, a SUM of the paypal transactions by each day,. 
I have tried to create a subquery but this returns a value for the entire TotalAmount column, not broken down by day.

MYSQL Sum Query with IF Condition

I need to write a query that returns the sum of all values that meet a certain criteria, but the query needs to return 0 if no rows are found, rather than null.  For example:

    tab    
    +---------------+-----+
    | descr         | num |
    +---------------+-----+
    | hello there   | 5   |
    | hi there      | 10  |
    | hello         | 10  |
    | hi there!     | 15  |
    +---------------+-----+

This query:

    SELECT sum(num) AS val FROM tab WHERE descr LIKE &quot;%hello%&quot;;

should, and does, return `15`.  However:

    SELECT sum(num) AS val FROM tab WHERE descr LIKE &quot;%greetings%&quot;;

should return `0`, but does return `null`.

Can someone explain if this is possible?

How to select sum -or- 0 if no records exist?

I have a data frame with several columns; some numeric and some character. **How to compute the&#160;sum of a specific column?** I’ve googled for this and I see numerous functions (`sum`, `cumsum`, `rowsum`, `rowSums`, `colSums`, `aggregate`, `apply`) but I can’t make sense of it all.

For example suppose I have a data frame `people` with the following columns


    people &lt;- read(
      text = 
        &quot;Name Height Weight
        Mary 65     110
        John 70     200
        Jane 64     115&quot;, 
      header = TRUE
    )
    …

How do I get the sum of all the weights?

How to sum data.frame column values?

I have a database table containing the following columns:

    id   code   value   datetime   timestamp

In this table the only unique values reside in id i.e. primary key.

I want to retrieve the last distinct set of records in this table based on the datetime value. For example, let&#39;s say below is my table

    id   code   value   datetime               timestamp
    1    1023   23.56   2011-04-05 14:54:52    1234223421
    2    1024   23.56   2011-04-05 14:55:52    1234223423
    3    1025   23.56   2011-04-05 14:56:52    1234223424
    4    1023   23.56   2011-04-05 14:57:52    1234223425
    5    1025   23.56   2011-04-05 14:58:52    1234223426
    6    1025   23.56   2011-04-05 14:59:52    1234223427
    7    1024   23.56   2011-04-05 15:00:12    1234223428
    8    1026   23.56   2011-04-05 15:01:14    1234223429
    9    1025   23.56   2011-04-05 15:02:22    1234223430
    
I want to retrieve the records with IDs 4, 7, 8, and 9 i.e. the last set of records with distinct codes (based on datetime value). What I have highlighted is simply an example of what I&#39;m trying to achieve, as this table is going to eventually contain millions of records, and hundreds of individual code values.

What SQL statement can I use to achieve this? I can&#39;t seem to get it done with a single SQL statement. My database is MySQL 5.

Get last distinct set of records

I&#39;m wondering what is the best way to display unique records from a has_many, through relationship in Rails3.

I have three models:

    class User &lt; ActiveRecord::Base
        has_many :orders
        has_many :products, :through =&gt; :orders
    end
    
    class Products &lt; ActiveRecord::Base
        has_many :orders
        has_many :users, :through =&gt; :orders
    end
    
    class Order &lt; ActiveRecord::Base
        belongs_to :user, :counter_cache =&gt; true 
        belongs_to :product, :counter_cache =&gt; true 
    end

Lets say I want to list all the products a customer has ordered on their show page. 

They may have ordered some products multiple times, so I&#39;m using counter_cache to display in descending rank order, based on the number of orders.

But, if they have ordered a product multiple times, I need to ensure that each product is only listed once.

    @products = @user.products.ranked(:limit =&gt; 10).uniq!
works when there are multiple order records for a product, but generates an error if a product has only been ordered once. (ranked is custom sort function defined elsewhere)

Another alternative is:

    @products = @user.products.ranked(:limit =&gt; 10, :select =&gt; &quot;DISTINCT(ID)&quot;)

I&#39;m not confident that I&#39;m on the right approach here. 

Has anyone else tackled this? What issues did you come up against? Where can I find out more about the difference between .unique! and DISTINCT()? 

What is the best way to generate a list of unique records through a has_many, through relationship?

Thanks




How to display unique records from a has_many through relationship?

    SELECT DISTINCT field1, field2, field3, ......   FROM table

I am trying to accomplish the following sql statement but I want it to return all columns is this possible? Something like:

    SELECT DISTINCT field1, * from table

SQL/mysql - Select distinct/UNIQUE but return all columns?

I am performing some tests on a `HSQLDB` server with a table containing 500 000 entries. The table has no indices. There are 5000 distinct business keys. I need a list of them.

Naturally I started with a `DISTINCT` query:

    SELECT DISTINCT business_key
    FROM memory
    WHERE concept &lt;&gt; &#39;case&#39;   OR 
          attrib  &lt;&gt; &#39;status&#39; OR 
          value   &lt;&gt; &#39;closed&#39;;

It takes around 90 seconds!!!

Then I tried using `GROUP BY`:

    SELECT business_key
    FROM memory
    WHERE concept &lt;&gt; &#39;case&#39;   OR 
          attrib  &lt;&gt; &#39;status&#39; OR
          value   &lt;&gt; &#39;closed&#39;;
    GROUP BY business_key

And it takes 1 second!!!

Trying to figure out the difference I ran `EXLAIN PLAN FOR` but it seems to give the same information for both queries.

`EXLAIN PLAN FOR DISTINCT ...`

    isAggregated=[false]
    columns=[
      COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
    ]
    [range variable 1
      join type=INNER
      table=MEMORY
      alias=M
      access=FULL SCAN
      condition = [    index=SYS_IDX_SYS_PK_10057_10058
        other condition=[
        OR arg_left=[
         OR arg_left=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
           VALUE = case, TYPE = CHARACTER]] arg_right=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
           VALUE = status, TYPE = CHARACTER]]] arg_right=[
         NOT_EQUAL arg_left=[
          COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
          VALUE = closed, TYPE = CHARACTER]]]
      ]
    ]]
    PARAMETERS=[]
    SUBQUERIES[]
    Object References
    PUBLIC.MEMORY
    PUBLIC.MEMORY.CONCEPT
    PUBLIC.MEMORY.ATTRIB
    PUBLIC.MEMORY.VALUE
    PUBLIC.MEMORY.BUSINESS_KEY
    Read Locks
    PUBLIC.MEMORY
    WriteLocks


`EXLAIN PLAN FOR SELECT ... GROUP BY ...`

    isDistinctSelect=[false]
    isGrouped=[true]
    isAggregated=[false]
    columns=[
      COLUMN: PUBLIC.MEMORY.BUSINESS_KEY
    ]
    [range variable 1
      join type=INNER
      table=MEMORY
      alias=M
      access=FULL SCAN
      condition = [    index=SYS_IDX_SYS_PK_10057_10058
        other condition=[
        OR arg_left=[
         OR arg_left=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.CONCEPT] arg_right=[
           VALUE = case, TYPE = CHARACTER]] arg_right=[
          NOT_EQUAL arg_left=[
           COLUMN: PUBLIC.MEMORY.ATTRIB] arg_right=[
           VALUE = status, TYPE = CHARACTER]]] arg_right=[
         NOT_EQUAL arg_left=[
          COLUMN: PUBLIC.MEMORY.VALUE] arg_right=[
          VALUE = closed, TYPE = CHARACTER]]]
      ]
    ]]
    groupColumns=[
    COLUMN: PUBLIC.MEMORY.BUSINESS_KEY]
    PARAMETERS=[]
    SUBQUERIES[]
    Object References
    PUBLIC.MEMORY
    PUBLIC.MEMORY.CONCEPT
    PUBLIC.MEMORY.ATTRIB
    PUBLIC.MEMORY.VALUE
    PUBLIC.MEMORY.BUSINESS_KEY
    Read Locks
    PUBLIC.MEMORY
    WriteLocks

---
## EDIT
I did additional tests. With 500 000 records in `HSQLDB` with all distinct business keys, the performance of `DISTINCT` is now better - 3 seconds, vs `GROUP BY` which took around 9 seconds.

In `MySQL` both queries preform the same:

MySQL: 500 000 rows - 5 000 distinct business keys:
Both queries: 0.5 second
MySQL: 500 000 rows - all distinct business keys:
`SELECT DISTINCT ...` - 11 seconds
`SELECT ... GROUP BY business_key` - 13 seconds

So the problem is only related to `HSQLDB`.

I will be very grateful if someone can explain why there is such a drastic difference.


Huge performance difference when using GROUP BY vs DISTINCT

I have a L2E query that returns some data that contains duplicate objects. I need to remove those duplicate objects. Basically I should assume that if their IDs are the same then the objects are duplicate. I&#39;ve tried `q.Distinct()`, but that still returned duplicate objects. Then I&#39;ve tried implementing my own IEqualityComparer and passing it to the `Distinct()` method. The method failed with following text:

&gt; LINQ to Entities does not recognize the method
&gt; &#39;System.Linq.IQueryable`1[DAL.MyDOClass]
&gt; Distinct[MyDOClass](System.Linq.IQueryable`1[DAL.MyDOClass],
&gt; System.Collections.Generic.IEqualityComparer`1[DAL.MyDOClass])&#39;
&gt; method, and this method cannot be translated into a store expression.

And here is the implementation of EqualityComparer:

  

      internal class MyDOClassComparer: EqualityComparer&lt;MyDOClass&gt;
        {
            public override bool Equals(MyDOClass x, MyDOClass y)
            {
                return x.Id == y.Id;
            }
    
            public override int GetHashCode(MyDOClass obj)
            {
                return obj == null ? 0 : obj.Id;
            }
        }

So how do I write my own `IEqualityComparer` properly?

Content Type	Original Author	Original Content on Stackoverflow
Question	Chris Denman	View Question on Stackoverflow
Solution 1 - Sqlite	Bhoom Suktitipat	View Answer on Stackoverflow
Solution 2 - Sqlite	Håvard	View Answer on Stackoverflow

How can I count how many duplicates there are for each distinct value in sqlite?

Sqlite Problem Overview

Sqlite Solutions

Solution 1 - Sqlite

Solution 2 - Sqlite

Set custom keybinding for specific Emacs mode

How to distribute ios application wirelessly without managing UDIDs and recompilation

Attributions